Dynamic Timeframes for Dimension Groups

Let’s say you want users to be able to change timeframes on the fly. Imagine that with a simple filter-style toggle, users can choose between rolling up their data at the date level, week level, or month level.

With parameters, this is totally possible! First, let’s assume you have a dimension group with the desired timeframes.

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

Next, let’s 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, let’s make a dimension that chooses a timeframe from our dimension group based on input from our 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).