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.