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
orinteger
. Make sure that all outcomes of your case statement have the same datatype. For example,${view.created_date}
would result in datatypedate
. Depending on your SQL dialect, you can useCAST(${orders.created_date} AS string/char)
(MySQL, BigQuery) orTO_CHAR(${orders.created_date}, 'YYYY-MM-DD')
(Redshift, Postgres).