Top N chart in QlikView
The requirement to show top n category is common request from business. For example, show the number one product sold in last 5 years. I will demonstrate a simple way to showcase building a straight table and use of dimension limit option available in QlikView.
In the data model, I have dimension Year, Product, Unit Price and Units sold. Your data model may be different and choose required fields as per your requirement
To your sheet add a new chart object and select Straight Table icon, Enter the text Top Product by Year in the Window Title text box.
Click on the Next button to continue.
On the Dimensions, select the two fields. In the data model above, I added Year and Product to the Used Dimensions. Click Next to continue.
On the Edit expression window, create the measure required. In our case, it is sales computed based on Units and Unit Cost.
Sum(Units * [Unit Cost])
Click OK to close the expression window.
In the Label, enter measure name (in our case Sales).
Click Next button to continue on the tabs.. On the Numbers, select the Money for the number format.
Click Finish to complete.
We will get a list of Products sold for the years.
To show the Top one product, right click on the chart object and select Properties -> Dimension Limits
Click on the Product, and check the checkbox Restrict which values are displayed using the first expression.
Enter 1 in the Show only largest.
Also, uncheck the Show Others checkbox. Click OK to continue.
It will show only the one product for each year that was top sold.
Note, I have turned off showing Total in the expression tab.
We can make the Top 1 to Top n using a variable and slider.
To create a new variable, from the top menu item Settings, select Variable overview…
Click on the Add button to create a new variable like vTop. Click OK to continue.
In the definition box, enter default value for example 5.
Click on to continue, and close the window.
Right click on the Straight Table chart, and select Properties -> Dimension Limits. Click on the Product, and replace 1 with the vTop
For every year, the top 5 products sold will be listed.
You can change the sorting to show sales by descending each year.
Use the Promote or Demote button to set the priority as show below.
On the Property -> General, change the window title to include the variable
= ‘Top ‘ & vTop & ‘ Product(s) by Year’
The final table should look like
Create a slider object to assign it the variable vTop.
Change the slider value, and see the number of product listing changes.