Grouping Dimensions by Rank

[mjgrier]

Basically I have a dimension that has many values and I want to make a pie chart that includes the top 5, 10, etc and then groups the rest into it’s own value of “All Other”. How would I do this?

[Eric_Lyons]

Hi @mjgrier,

The one option would be to create a case when where we change the dimension such that we only show the individual values for the top n values, then group everything else as other. Then, we can just select this dimension in the explore and select the corresponding measure. 
 

We could use the same principle with a custom dimension. Instead of a case when, we would want to use an if statement. 

If you do not have LookML or custom field access the alternative would be to use a series of table calculations. We can use an if statement where we show the individual value based on the row (in my example I sorted by count descending). Then, if it above a specific row number, I use a sum and a list offset function to aggregate the other counts. 

 

I then need to change the label of the state after it exceeds my row threshold. I can use another case when to do this. 

Finally, I need to hide the rows that I do not want to show and I use a third table calculation with an if statement that returns a yesno value. Then, I click the gear icon on this column and select Hide No’s from Visualization. 

Please let me know if you have any questions.

Thanks,

Eric

[mjgrier]

Hey Eric,

Thanks for the reply, I need this to change dynamically as the top 10 will very likely change on a month to month basis, so I think the best course of action would be the table calculation route correct?

[eric_h]

I think the table calculation route that Eric mentioned is the easiest to implement, but you can also add Top N analysis to your LookML, as well!

There’s a great article that shows how you can dynamically select the top 5, 10, 15, however many you want, while packing everything else into an “Other” category. It’s essentially doing the same thing as the table calculations, but packs that information into derived tables within your model.

He does a great job explaining it with code examples, so I won’t plagiarize it here 🙂