View the original community article here
Last tested: May 28, 2019
You may be tasked with this kind of question:
“Which are the top 5 states in terms of order count for each month in our history?”
Can I answer this question in Looker and clearly visualize it? Yes, definitely you can! We will solve it with table calculation:
- Select the month and states dimension and the orders count measure. We will pivot on the state dimension.
- To find out the top 5 states, we will use two functions: pivot_row() and large(): using large() we can get the nth biggest value of a list, and pivot_row() can provide us the list of the values in each row.
- When we get the 5th largest value of the list, we then compare the measure with the 5th largest value of the row, if it equals or greater than this value, then we show it, otherwise we let it show null. Don't forget to hide the original measure from the visualization.
if(${orders.count}>=large(pivot_row(${orders.count}), 5), ${orders.count}, null)
- In the visualization, we turn off “plot null values” and turn on “Null values cause discontinuities”
Voila, now you can show this result!
Last but not least, be aware of the column limit in the explore, we have max 200 column limit, however you will start to see warnings of column limit when you have over 50 columns. For the best of performance and story telling, we always recommend less columns for readability of the visualization. For more about row and column limit: https://docs.looker.com/exploring-data/filtering-and-limiting#limiting_data
This content is subject to limited support.