QlikView offers quite a few ways to easily sort dimensions in your listboxes, tables and charts: by frequency, numeric, text or load order. But what if you want to use a custom sort order that does not follow one of these patterns?

Consider, for example, the following scenario; we have three Business Lines: A, B and C. These Business Lines always need to appear in the order B, A, C. This type of ordering cannot* be achieved by using any of the default sort orders, we will have to create a custom sort order. This post will describe two methods for doing this.

You can download the example qvw by clicking here.

Method 1: Assigning a sort order value in the load script
This method works by assigning the sort order value in the load script.

BusinessLines:
LOAD * INLINE
[
Business Line, Sort order
Business Line A, 2
Business Line B, 1
Business Line C, 3
];

The example script above shows an additional field ([Sort order]) that defines the sort order. This field can now be used in the sort expression field of your listbox, table or chart, as shown in the image below.

If you want to make this sort order the default sort order for a field, you can also add it to the sort tab of your document properties (via Settings -> Document properties or via Ctrl + Alt + D).

Method 2: Using the match function
The match function takes an input value and a range of values to compare it against and returns the number of the value that it matched, or 0 if no value was matched.

Using this function we can define the following expression:

=match([Business Line], ‘Business Line B’, ‘Business Line A’, ‘Business Line C’)

This expression will return 1 for Business Line B, 2 for Business Line A and 3 for Business Line C.  This expression can now be used in the sort expression field, as shown in the image below.

Keep in mind that the match function does a case-sensitive comparison, so “business line a” would not be matched. For a case-insensitive comparison use the mixmatch function. To include wildcard characters use the wildmatch function.

Which method to use?
So, which method should be used when? Generally, the method of assigning a sort order value in the load script is the preferred method, especially if you have to sort many values. However, in cases where you cannot (for example, in the case of a calculated dimension) or do not want to add a field to the data model the match function method is the best choice.

You can download the example qvw by clicking here. If you have any questions, feel free to ask them in the comments section.

_{* If you load the data in the desired order from the source, you could also use a load order sort.}

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.