Coming Soon! We’re launching a new sub-community within the Google Cloud Community dedicated to cloud security: The Google Cloud Security Community. In preparation for the launch, this site will be in read only mode from 22 September 12am PST - 23 September 7pm PST

How do I find the top 5 XYZ order count of each {timeframe}

Knowledge Drop

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:

  1. Select the month and states dimension and the orders count measure. We will pivot on the state dimension.


  1. 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.
  2. 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)

  1. 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:

This content is subject to limited support.                

Version history
Last update:
‎04-05-2021 03:17 PM
Updated by: