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

There are 3 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:

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"
 }
 }

Using SQL CASE WHEN for Bucketing

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,  then it is recommended to use a SQL CASE WHEN.

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