The Problem
When we want to look at our data in terms of 30-day periods, we can use Looker's built in month
timeframe for dimension_group
fields. However, this can pose a problem, as not all months are the same length. Also, in cases where the current day is in the middle of a month, the data for that "period" is incomplete. The goal in this case is to get a period for every 30 days (i.e., past 0-30 days = 1, past 30-60 days = 2, 60-90 days = 3, etc.), starting from today and moving backwards, by which to group our data.
The Solution
In Looker, we can achieve this pattern by placing a SQL function in the SQL:
parameter of a dimension. For example, let's say that we have a date dimension_group
called created
:
dimension_group: created {
type: time
timeframes: [time, date, month, raw]
sql: ${TABLE}.date ;;
}
Then, all we need to do is create a new dimension of type: number
to capture the number of days between the created
date and the current date. We can do this by using a DATEDIFF
function in the new dimension, and then dividing the result by 30.
dimension: periods_ago
type: number
sql: FLOOR(DATEDIFF(CURRENT_DATE(), ${created_raw}) / 30);;
This will allow us to group by these 30-day periods, which will be output as 1 for within 30 days ago, 2 for within 30-60 days ago, 3 for within 60-90 days ago, etc., in Explores. Note that FLOOR
, DATEDIFF
, and CURRENT_DATE()
can all vary by dialect and database, but there should be an equivalent in pretty much every dialect of SQL.
Also note the use of the raw
timeframe, which is important for date calculations because of the way Looker casts and converts time zones.
Lastly, we can customize the period length by replacing "30" with the desired number of days.