30 Day Windows: Periods Starting from Today

  • 22 June 2022
  • 1 reply
  • 562 views

Userlevel 5
Badge
  • Looker Staff
  • 172 replies

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

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.


This topic has been closed for comments

1 reply

Hi, 

Thanks for this; very helpful!

I am curious how can you “periods_ago” dimension to created dimension group? Can  we?