Dynamic timeframes for Dimension Groups

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

• First, assuming we have a dimension group with the desired timeframes:

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

• Next, set up a parameter which 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 a string, as shown in the Note below.
  

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 integer. Make sure that all outcomes of your case statement have the same datatype. For example, ${view.created_date} would result in 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).