Custom Sort in Qlikview

In one of my dashboards,  user asked me to create a pivot table that displays assets for top 10 dimension values and everything else grouped and displayed under 'Other'. So, at most the pivot table will have 11 values displayed in the dimension. This requirement can easily be achieved by creating calculated dimension using aggr () and rank () function as explained in my earlier post.
However, the tricky part was the custom sorting requirement. The dimension values need to be sorted in alphabetical ascending order but 'Others' should to be the last value.
After trying several work arounds, I solved custom sorting requirement using match () function. Under Sort tab in chart properties, selected Expression and  used following expression:

Match (aggr (if (rank(sum (Assets))<=10, Currency,'Others'), Currency),'1','2','3','4','5','6','7','8','9','10','Others')

I also selected Text A -> Z.

As you may know, Match() takes input string (aggr() expression in this case) and range of values. It returns the position of the input string in the range values. If the string is not found, it returns 0. In this scenario, it simply returns 11 for 'Others' and 0 for everything else.

This made 'Others' to be sorted as last value (11th position) and everything else is sorted in alphabetical order.

Custom sorting in Qlikview can also be achieved by creating an inline table with 2 columns, containing dimension value (same name as actual dimension column) and sort order ( numeric column with sort order numbers).  Qlikview associates dimension column with actual dimension in the data model. While sorting in UI, we can select expression and use the sort order column from the inline table for custom sort order. 

But , this was a different case as the dimension values are constantly changing and too many dimensions to code the inline table.

Feel free to comment if you know any other alternative or better solutions

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])

Line Chart Trick to Avoid Horizontal Scroll Bar

Often line chart in Qlikview apps  are displayed partially due to space limit on the sheet. When the chart is stretched the the lines starts appearing completely.
To make the lines on the chart appear completely either chart needs to stretched or chart properties needs to updated to have horizontal scroll bar (presentation tab).
Horizontal scrolls works most of the time but they are annoying sometimes.
Do you want to display the line on the chart completely without stretching or scroll bars?
Follow the simple trick to cheat Qlikview to make it display complete chart without horizontal scroll bars.

1) Right  click on the line chart and open the Properties.
2) In General , select Bar Chart as Chart Type
3) In Presentation tab,  select"Show All Bars" check box.
4) Now, go back to General tab and select Line Chart as Chart Type.
5) Click OK

Line chart now displays complete line and horizontal scroll bar is suppressed.

Hope this helps!