Creating a pie chart with over 50 rows using table calcs to create an "Other" bucket

[Pieter_DeVries]

It is sometimes the case that we need to create a pie chart, or any other chart that has row limits, but our raw data exceeds those limits.

We can always make Lookml or SQL Database changes to create buckets, such as seen here:

https://community.looker.com/lookml-5/create-an-other-bucket-in-a-chart-1100

but that doesn’t always match our work flow.

My solution is an explore-only purely table calc driven solution.

There are 3 main parts.

1. Creating a new dimension with “Others”, notice it is important to sort on your measure to make sure “others” matches the criteria: 
   states_with_others: if(row()<50,${events.state},"Others")
2. Creating a hide column, that allows us to use yes,no to hide certain rows so that they stay within our requirements:
   hide_after_50: if(row()>50,no,yes)
   This uses the “Hide No’s from Visualization” solution seen here: https://docs.looker.com/exploring-data/using-table-calculations#using_table_calculations_in_visualiz...
3. Integrating non-”other” dimensions with “other” dimensions:
   count_with_others: if(${states_with_others}="Others",sum(if(${states_with_others}="Others",${events.count},0)),${events.count})

What this allows us to do is to set a row amount that we want to appear in the visualization, and have all rows after that be grouped dynamically into the “Others” bucket, and have their measure data be rolled dynamically into the new measure table calc.

This can help create bucketing prototypes, or help out explore users who cannot change the lookml/backend sql.

[vinod_kumar-167]

row() function is not working in sql expressions in looker view.