Starting in Looker 21.12, you can create ad hoc custom groups for dimensions without using logical functions in Looker expressions or developing CASE WHEN logic in sql parameters or type: case fields when the Custom Fields Labs feature is enabled.

Starting in Looker 21.14, users can create ad hoc custom bins for numeric type dimensions without needing to use logical functions in Looker expressions or needing to develop type: tier LookML fields when the Custom Fields Labs feature is enabled.

Bucketing can be very useful for creating custom grouping dimensions in Looker.

There are three ways to create buckets in Looker:

1. Using the tier dimension type
2. Using the case parameter
3. Using a SQL CASE WHEN statement in the SQL parameter of a LookML field

Using tier for Bucketing

To create integer buckets, we can simply define the dimension type as tier:

dimension: users_lifetime_orders_tier {
 type: tier
 tiers: [0,1,2,5,10]
 sql: ${users_lifetime_orders} ;;
}

You can use the style parameter to customize how your tiers appear when exploring. The four options for style are:

1. classic
2. integer
3. interval
4. relational

For example:

dimension: age_tier {
 type: tier
 tiers: [0,10,20,30,40,50,60,70,80]
 style: integer
 sql: ${age} ;;
}

The style parameter classic is the default:

The next image shows examples of the style parameters interval, integer, and relational:

Things to Consider

Using tier in conjunction with dimension fill can result in unexpected tier buckets.

For example, a type: tier dimension, Age Tier, will display tier buckets for Below 0 and 0 to 9 when dimension fill is enabled, although the data does not include age values for those buckets:

When dimension fill is disabled for Age Tier, the buckets more accurately reflect the age values available in the data:

You can enable or disable dimension fill by hovering over the dimension name in the Explore, clicking the field-level gear icon, and selecting either Remove Filled in Tier Values to disable, or Fill in Missing Tier Values to enable.

Find out more about Looker tiers in the documentation.

Using case for Bucketing

You can use the case parameter to create custom-named buckets with custom sorting. The case parameter is recommended for a fixed set of buckets, as it can help control the way values are presented, ordered, and used in UI filters and visualizations. For example, with case, a user will be able to select only the defined bucket values in a filter.

To create buckets with case, we simply define a dimension, like so:

dimension: order_amount_bucket {
 case: {
 when: {
 sql: ${order_amount} <= 50;;
 label: "Small"
 }
 when: {
 sql: ${order_amount} > 50 AND ${order_amount} <= 150;;
 label: "Medium"
 }
 when: {
 sql: ${order_amount} > 150;;
 label: "Large"
 }
 else:"Unknown"
 }
 }

Case will typically sort values in the order in which the buckets are listed. This would look something like:

If you would like to sort alphanumerically, add the alpha_sort parameter to the dimension, like so:

dimension: order_amount_bucket {
 alpha_sort: yes
 case: {
 when: {
 sql: ${order_amount} <= 50;;
 label: "Small"
 }
 when: {
 sql: ${order_amount} > 50 AND ${order_amount} <= 150;;
 label: "Medium"
 }
 when: {
 sql: ${order_amount} > 150;;
 label: "Large"
 }
 else:"Unknown"
 }
 }

For dimensions where many distinct values are desired in the output (this would require you to define each output with a WHEN or an ELSE statement), or when you would like to implement a more complex ELSE statement, we recommend using a SQL CASE WHEN, discussed in the section below.

Read more about the case parameter in Looker's documentation.

Using SQL CASE WHEN for Bucketing

A SQL CASE WHEN statement is recommended for more complex bucketing, or for implementation of a more nuanced ELSE statement.

For example, you may want to use different bucketing methods, depending on the destination of an order. A SQL CASE WHEN statement could be used to create a compound bucket dimension, where the THEN statement returns dimensions rather than strings:

dimension: compound_buckets {
 sql:
 CASE
 WHEN ${orders.destination} = 'US' THEN ${us_buckets}
 WHEN ${orders.destination} = 'CA' THEN ${canada_buckets}
 ELSE ${intl_buckets} 
 END ;;
}