Knowledge Drop
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
LinkedIn
Twitter