Top N in Qlikview Straight/Pivot Tables and Charts

Often we want to display Top N values in Qlikview straight/pivot tables and charts. This requiers restricting dimension in the chart/table to display Top N records based on the metric.
We can use aggr( ), rank() and sum() functions together in dimension to achieve the same.

For example, Create a Pivot table to display Gross Revenue and Gross Room Nights for Top N (top 10 in this example) Hotel Markets based Gross Room Nights.

Right on the sheet and Add New Sheet Object --> Chart
Select Straight Table and click Next button.

Dimension: Click on Add Calculated Dimension and add the expression
aggr(if(rank(sum([Gross Room Nights]))<=10,[Hotel Market]),[Hotel Market])

This will limit Hotel Market to Top 10.




Displays Top 10 Markets based on the gross room nights metric. Remaining markets are displayed in single record as null with '-' symbol in the table.
You can suppress the null record by Clicking on the dimension and selecting Supress When Value Is Null option on Dimensions tab

Label the calculated dimension as Hotel Market.

Click on Next button and add following expressions and label them appropriately.

Expression 1 : sum([Gross Room Nights])
Expression 2 : sum([Gross Revenue])

Click on Finish button.

Table now displays Top 10 markets and Gross Room Nights and Gross Revenue for the markets.

Hope this example helps!

Note: Above expression can be sightly altered to display Top 10 Hotel Markets and everything else labeled as 'Others'.
aggr(if(rank(sum([Gross Room Nights]))<=10,[Hotel Market],'Others'),[Hotel Market])