Date scale switching on charts (switch between weekly, monthly, etc. on the fly)

  • 12 January 2018
  • 2 replies

Userlevel 2

Been waiting for looker to come out with this feature but couldn’t wait any longer. You can use parameters and some logic on a date dimension to allow a drop down filter to modify the time scale on any chart. This doesn’t change the actual time scale but it modifies dates on records to group them together based on the month/day/year/etc. Go from monthly to weekly to daily in a couple clicks without having to edit the chart in an explore.

This is made possible by using parameters and liquid variables in the sql section of my desired dynamic date dimension. It also required that your flavor of sql has functions to return day of week, day of month, day of year, etc. We use Snowflake so that’s what you’ll see here:

parameter: date_part {
type: unquoted
allowed_value: {
label: "Years"
value: "DAYOFYEAR"
allowed_value: {
label: "Weeks"
value: "DAYOFWEEK"
allowed_value: {
label: "Months"

dimension: responded_dynamic_date {
type: date
sql: DATEADD(d, (-1 * {% parameter date_part %}(${TABLE}.date_field) + 1), ${TABLE}.date_field) ;;

2 replies

Userlevel 5

Awesome post. Related: [Retired] Dynamic Timeframes for Dimension Groups

Thank you for the great solution!

If you use Snowflake it’s easier to use

sql: DATE_TRUNC({% parameter date_part %}, ${TABLE}.date_field) ;;