End-user-defined dynamic tiers

[white1]

Looker’s tier dimension type is great for defining buckets for breaking out numeric dimensions. For example, how are 30-50yr olds behaving compared with the over 50s? By default, these tiers are fixed in LookML, and are consistent across all reports and dashboards.

But sometimes, we might want to compare different tiers based on a quick hunch or gut-feeling, and come up with our buckets on the fly. For example, how does our 30-50yr old group further break down into sub-groups (eg. 30-35, 35-40)?

Whats more, our less-technical dashboard users may want to do the same thing. Wouldn’t be great if we could just type in a list of numbers and get buckets based on this?

Hurray! Using LookML and Liquid, we can do exactly this. This pattern allows a user to input a comma-delimited string (eg 18, 30, 40, 50, 70) and Looker will generate a set of SQL-based buckets to reflect this. The new tier is a first-class dimension, and so can be used as a pivot, and with any combination of other dimensions and measures.

To do this, we’ll need to create a parameter of type string to capture the input, and another dimension age_compare_groups which will contain our auto-generated tiers, which will reference the parameter.

The following LookML contains everything you need 🔧

Note: The SQL syntax for this example may need to be adapted to suit your database dialect.

# A user's age. Just a number in our table
  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
  
# This is our parameter which our user will populate
  parameter:  age_buckets {
    type: string
  }
  
# This dimension uses liquid (the {%} stuff) to do some parsing and builds a case statement
  dimension: age_compare_groups {
    sql:
    {% assign my_array = age_buckets._parameter_value | remove: "'" | split: "," %}
        {% assign sort = '-1' %}
    {% assign last_group_max_label = ' 0' %}

    case
    {%for element in my_array%}
    {% assign sort = sort | plus: 1 %}

      when ${age}<{{element}} then '{{sort}}. {{last_group_max_label}} < N < {{element}}'
      {% assign last_group_max_label = element %}
    {%endfor%}
    {% assign sort = sort | plus: 1 %}

      when ${age}>={{last_group_max_label}} then '{{sort}}. >= {{last_group_max_label}}'
    else 'unknown'
    end
          ;;
  }