Showing Top x Values in a Pivot Table

One of the benefits of utilizing a straight table over a pivot table is the ability to limit the dimension values. With a straight table it’s quite easy, simply click on the Dimension Limits tab, under the Limits section, check the Restrict which values are displayed using the first expression check box. Select the show only radio button and enter in the number of values you would like to show.

Pretty simple, but what if we want to use a pivot table? You’ll notice the Dimension Limits tab is not available to us in the pivot table chart properties. Fear not my fellow QlikView designers! Although displaying x number of values is not as straightforward with a pivot table, it is still possible with the use of advanced aggregations. Follow along to see how.

Create your pivot table, in this example I’ll be using state and year as the dimensions and Sum(Revenue) as the expression.

I want to limit the number of states shown in the chart, so right click to edit the chart properties. On the Dimensions tab, select state under used dimensions, and click the edit button.

Enter the following expression:

Click OK. There are now only four dimension values shown. The top 3 states by revenue with the fourth value being an ‘Others’ bucket, which is where all of the states outside of the top 3 are grouped together

If you only want to see the top 3 states without showing ‘others’, then edit the dimension property again and change the dimension expression to the following

Before clicking OK make sure to turn on the ‘Suppress When Value is Null’ option. Now only the top 3 states are displayed.

This is a pretty straightforward example of how powerful the Aggr function is in QlikView. In this example, I used Aggr to create an in-memory table chart of the rank of revenue for each state. From there the results were compared to see if they were <=3. If it was, the If statement returned the state, otherwise it returned something else, either Others or Null.