In the following post, I describe how to setup a Qlikview Custom Sort Order, which allows you to control the way a field is sorted and displayed. Whether it be in a list box, chart or visual, as a designer you want to make sure the data is presented exactly the way it is intended. You would think that something as basic as sorting a value in a chart would be quite simple. However, this seemingly innocent sort can turn out to be quite painful. If you ever find this task to be difficult, the following steps will solve your problems!
How to implement Qlikview Custom Sort Order
In the following example, I have a table with fantasy football stats, including a player’s weekly points total. I wanted to setup a way to see the number of times each week/year that player points fell into a range of values. I created an additional column in my dataset called PtsBucket, which buckets each data point in the following buckets:
- < 0
- 0 pts – 5 pts
- 5 pts – 10 pts
- 10 pts – 20 pts
- 20 pts – 30 pts
- > 30 pts
The resulting data is loaded, then I setup a simple chart like the following, displaying the count of instances a player’s point total fell within the buckets identified above:
Simple enough, right? No, it is quite the opposite and quickly you notice that something is wrong. The values in the Range field are not lining up properly. You expect to see the “> 30 pts” bucket at the bottom, but it does not get sorted this way by default. You play around with the sort ordering in the chart properties, but you can’t get it right.
I’ve seen examples where designers add an additional column (like an order identifier that is numeric), add it to the chart, then order the chart by is numeric value to get the desired visual. This is not optimal, and it doesn’t solve the problem when you want to display the bucket in a List Box.
To fix this issue, you can dictate exactly how these field values sort by adding a simple block of text in your LOAD script, which looks like the following:
Load * Inline [
0 pts - 5 pts
5 pts - 10 pts
10 pts 20 pts
> 30 pts
The result of the statement above adds an Inline table called “PtsBucket_SortOrder”. The sole purpose of this Inline table is to establish how the values in field PtsBucket are loaded into your Qlikview application. Qlikview stores this as the “Original” Load order for the values in this field, and you can then use this as the primary sort for any object within your application, based on the given field (in my case, PtsBucket).
As a result, I can now go back into the properties of my chart and make the following change:
Hence, the chart below now sorts the Range values in the proper way!
Also, the same is applicable to the List Box:
In addition, if you are particular about keeping your data model clean in the Table Viewer, you can simply drop the “_SortOrder” table(s) after your main LOAD in the script:
Drop Table PtsBucket_SortOrder;
Finally, make sure you drop the temporary table AFTER you load the data where your field exists.
As a result of using the technique illustrated above, you can easily implement a Qlikview Custom Sort Order into your application.
There are a few practical uses for this functionality:
- Displaying the proper sort order in a Chart
- Displaying the proper sort order in a List Box
I hope you find this post helpful.