Knowledge Drop

Dynamic rolling week buckets

  • 6 April 2021
  • 0 replies
  • 313 views

Userlevel 5

Last Tested: Mar 6, 2019

 

Objective:

To compare data over a dynamic 7 day timeframe/buckets

(For example if today is 15th March, the 7 day bucket is from 8thMarch-14th March, next bucket is from 1st March - 7th March etc for the entire data

Why:

Sometimes you want to do a complete 7 days(excluding today) vs previous 7 day analysis of sales.
Currently when we use 'week' timeframe in the dimension_group, it is based on the default 'week-start-date'. And there is not a way to change the week-start-date dynamically to 'yesterday' everyday

How:

This can be accomplished by creating a new dimension using some hacky sql within it.

So here, we are taking -1th day(Yesterday) for 8days (using interval) (-1+8=7days) and that puts in this specific date across all rows. This dimension can then be selected in our explores for dynamic grouping.

dimension: adjusted_week_dimension {

type: string

datatype: datetime

sql: TO_CHAR(DATE_TRUNC('week', (created_date )::timestamptz AT TIME ZONE 'America/Los_Angeles'-((EXTRACT(DOW FROM NOW() AT TIME ZONE 'America/Los_Angeles')-1)||' day')::interval)+interval '8 day', 'YYYY-MM-DD') ;;

}

 

PS:

If the dynamic tiers need to depend on the user input, we can use the method suggested in this Help Center Article: https://help.looker.com/hc/en-us/articles/360023857093-Creating-Dynamic-Tiers

 

This content is subject to limited support.                

 


0 replies

Be the first to reply!

Reply