How can I dynamically change my dimension timeframe based on the dates being filtered?

[kevin_dunn]

Knowledge Drop

Last tested: Sep 25, 2019

Sometimes its nice to have your dimension_groups timeframe update automatically based on the date range in the filter. For instance, if we are looking at one year of data, show the data by month, If we are looking at a month of data, show it by week, if we are looking at a week, show it by individual date, etc. We can do this utilizing datediff functions and date_start and date_end liquid parameters that reference our dimension group, and then using a case statement to display different timeframes based on how large the range is.

We need a standard dimension group, and a second string dimension that selects different timeframes using a case statement:

dimension_group: created {
type: time
hidden: yes
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: ${TABLE}.created_at ;;
}

dimension: dynamic_date {
label: "Dynamic Date"
type: string
sql:
CASE
WHEN datediff({% date_end created_date %}, {% date_start created_date %}) < 30 THEN ${created_date}
WHEN datediff({% date_end created_date %}, {% date_start created_date %}) > 365 THEN ${created_year}
WHEN datediff({% date_end created_date %}, {% date_start created_date %}) > 90 THEN ${created_month}
WHEN datediff({% date_end created_date %}, {% date_start created_date %}) > 30 THEN ${created_week}
ELSE ${created_month}
END ;;
}

This second dimension references the filter from the dimension group, so it's important that the dimension_group is filtered in order to use this field, you can use always_filter in the explore to make sure it's always there. The datediff syntax will vary based on the dialect.

This content is subject to limited support.