Other Buckets Using Table Calculations

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.

When you are building a chart or table off a high-cardinality dimension, you might want to lump all low-frequency dimensions into an Other bucket to avoid cluttering your output. Referencing the :total of a measure in a table calculation makes other bucketing possible without the need for any LookML coding.

Starting off with a count of events on our Looker instance:

The top two events make up the bulk of our events, but well over a hundred other events make up the remainder. A pie chart with this table would be very messy, so we can add a row limit to display a more manageable data set:

Any visualization we build off this table at this point would be misleading, because we have excluded events outside the top 10.

Using table calculations, however, we can replace the last row in this table with the sum of events from all rows excluded from the table:

1. First, make sure to turn on column totals (this is the Totals check box next to Row Limit), so we can access the total count of events in table calculations.

2. Then, add a table calculation to display the event count for all non-displayed events in the last row of the table:

   if(
   # For rows other than the last row
    row() != max(row()) 
   # Display event count
    ,${events.count}
   # For the last row, display total event count from all rows above this one
    , ${events.count:total} - sum( if(row()= max(row()) , 0 , ${events.count}) )
   )

3. Add another table calculation to display "Other" as the count in the last row:

   if(
   # For rows other than the last row
    row() != max(row()) 
   # Display event name
    ,${event.name}
   # For the last row, display "Other"
    , "Other"
   )

4. Hide the original dimension and measure, and choose the visualization option of your choice:

   

Because the logic to determine the last row is based off the current maximum row number, any changes to the row limit will flow through to the other bucketing logic automatically.