Dynamic Timeframes for Dimension Groups

You can use parameters to allow users to change timeframes on the fly, like a filter-style toggle where users can choose between rolling up their data at the date, week, or month level.

First, assume we have a dimension group with these desired timeframes:

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

Next, set up a parameter that suggests the timeframes we want. Keep in mind that defining a parameter by itself doesn't do anything; it will just provide a text box in the UI.

parameter: timeframe_picker {
  label: "Date Granularity"
  type: string
  allowed_value: { value: "Date" }
  allowed_value: { value: "Week" }
  allowed_value: { value: "Month" }
  default_value: "Date"
}

Finally, create a dimension that chooses a timeframe from the dimension group, based on input from the filter. It's OK for this dimension to be of type: string, as long as the timeframes you choose all return strings. If they return different types, you can cast them to strings, as shown in the note below.

Note: Because the resulting dynamic dimension is type: string, dimension fill cannot be applied to the dynamic_timeframe dimension in an Explore.

dimension: dynamic_timeframe {
  type: string
  sql:
    CASE
    WHEN {% parameter timeframe_picker %} = 'Date' THEN ${orders.created_date}
    WHEN {% parameter timeframe_picker %} = 'Week' THEN ${orders.created_week}
    WHEN{% parameter timeframe_picker %} = 'Month' THEN ${orders.created_month}
    END ;;
}

Now you can change up the parameter to select your timeframe!

Note: Looker converts certain timeframes to string values, while others remain of type: date or type: integer. Make sure that all outcomes of your case statement have the same datatype. For example, ${view.created_date} would result in the datatype date.

Depending on your SQL dialect, you can use CAST(${orders.created_date} AS string/char) (MySQL, BigQuery) or TO_CHAR(${orders.created_date}, 'YYYY-MM-DD') (Redshift, Postgres).