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

Some Useful Tools and Links

In this post , I'm trying to document some useful Qlikview tools that I often use to make Qlikview application development better, faster and easier.

Nested If-statements Generator

How many times you have struggled get a complex, nested if statements correctly. This online tool by Stefan Walther makes it quite easy to write the nested If statements.
Tool link
Documentation

Set Analysis Wizard for Qlikview

Stefan Walther created one more online tool to create Set analysis expressions along with documentation.
Tool link
Documentation

EasyQlik Qviewer

Often we run into situation where we need to open and analyze the QVD files. To examine the QVD content, the only easy way is to load the QVD into QVW.
EasyQlik Qviewer comes handy in these situations. It is a fast and lightweight standalone file viewer for analyzing Qlikview's QVD files. This helpful tool has many features apart from opening the QVD files.
It speeds up analysis and debugging and saves lot time. Though Its a paid tool, trial version is available with some limitations.
Link

Document Analyzer And Other Useful Tools

Rob Wunderlich has developed some of the very useful Qlikview tools. I particularly use
DocLog Analyzer. This tool will load qvw document logs from a directory and provide visualization of where time is spent in a script.
Link to All Tools

Qlikview Optimizer

This application uses .mem file to provides insight into RAM usage of qvw document. 

Tool link

QlikView Notepad++ Language Definition

This custom language definition for Notepad++ developed by qlikviewaddict provides convenient platform to edit qlikview load scripts and qvs files. The QlikView Custom Language Definition for Notepad++ gives basic syntax highlighting, auto-completion and code tool-tips when working with QlikView script files in Notepad++.
Tool link

Qlikview Data Profiler

This handy tool by Steve Dark provides quick and easy way of viewing the data that is in the qvw application. It is basically a QVW file with list box, table and other objects. These objects can be copied and pasted into any Qlikview application to view the data of that document.

Link to Data Profiler Page

Qlikview Power Tools

Power Tools is a small utility program or application that aid in performing a specific functionality or that extends QlikView functionality in some other way. The Power Tools are not supported and is seen as use-as-is tools, supplied separate from the QlikView product suite.
These tools are mostly helpful in managing server side activities.

Link to Power Tools Page

Qlikview Server CAL Manager

This application provides insights into server CALs allocation and usage. It can also delete the unused CALs after a defined number of days.

Link to Download

I'm sure there are many other useful tools that's missing from the list. Feel free to comment if you know of any.