Looker will not be updating this content, nor guarantees that everything is up-to-date.
You can create ad hoc custom groups for dimensions without using logical functions in Looker expressions or developingCASE WHEN
logic insql
parameters ortype: case
fields when the Custom Fields Labs feature is enabled and you have permissions to create custom fields.
You can also 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 and you have permissions to create custom fields.
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:
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}) )
)
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"
)
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.
Hi, This does not work when I added another dimension ‘Created Date’ and pivot the ‘Event Name’. I noticed that it will only work for one dimension with out pivot. Let me know your thoughts for two dimensions and a metric for Top 10 and Other.
This worked perfectly for what I had in mind!