Tiers can be a great way to bucket values, but with LookML type: tier
dimensions, those buckets are predefined and static. Sometimes we will want to create a dynamic tier that allows us to change the bucket size. We can do this using filter-only fields in Looker called parameters
in conjunction with a templating language called Liquid.
How to Do It
- First, create a parameter of
type: number
that will serve as the front-end filter field for the user to input the numerical bucket size they would like. - Then, reference the parameter value with the liquid variable
{% parameter parameter_name %}
within a dimension that will be used to determine the various buckets. This will dynamically change the output of the dimension according to the user input into theparameter
.
Example
In this example, we will build a dynamic age tier:
parameter: age_tier_bucket_size {
type: number
}
dimension: dynamic_age_tier {
type: number
sql: TRUNCATE(${TABLE}.age / {% parameter age_tier_bucket_size %}, 0)
* {% parameter age_tier_bucket_size %} ;;
}
Note: The SQL syntax for this example may need to be adapted to suit your database dialect.
To understand the logic, let's say an end user inputs the value '10' into the age_tier_bucket_size
filter on an Explore, indicating they wish to see the data grouped into 10 year buckets.
The SQL expression in the dynamic_age_tier
dimension will take a value from the underlying ${TABLE}.age
column - for example, 25 - and divide the age value by the inputted parameter value of 10, to get 2.5. This value is then truncated to 2 with the TRUNCATE
function and is multiplied by 10, to get 20. This becomes the bucket: any age value between 20 and 29 will be grouped into the '20' bucket.
Here it is what the output of this example would look like: