Qlikview Sorting by Multiple Expressions

I was recently working with a client who needed to use a list-box rather than a straight table to show a list of values and some measures. The reason for the list-box was that, unlike a chart, when the user makes selections, we can set it up so that all values continue to be shown seamlessly. The user can then add or remove items from their selection, affecting other objects on the sheet.

In this similar example, I created a list box with all Vendors. Using expressions in the list box I was able to create 2 extra columns. Using clear text boxes I was able to simulate column titles/labels for these expressions (because list-boxes do not allow me to assign labels for these expressions like tables do).

expression_sort 1.1

You can see that some of the vendors are assigned a preferred status = ‘Y’, based on criteria that was set up in the expression. Additionally I can see the sales corresponding to products acquired through each vendor.

I would like to sort / order the list first by status, then by sales amount. By default, list boxes are sorted alphabetically, as in the picture above.

The Sort tab in the list box properties allows me to declare an expression to sort by. I can enter the expression for status or sales:

Status: if(count({1}distinct ProductID)>=10 and count({1}distinct TerritoryID) >=10,’Y’,’ ‘)

Sales: sum({$<Vendor=>}LineSalesAmount

But how do I first sort by status then by sales?

The expression sort requires a single number for each vendor to sort by. I can make an expression that adds several values together come up with a single value.

If I can ensure that the vendors who meet the preferred status criteria always come up with a higher value than those who do not meet this criteria, then they will be sorted at the top.

expression_sort 1.2

I alter the status expression to assign a large value to vendors who meet the criteria. Since I want this number to always be sorted ahead of sales I just need to make sure the number is higher than any sales (keeping in mind that sales may increase in the future)

IF(COUNT({1}DISTINCT ProductID)>=10 AND COUNT({1}DISTINCT TerritoryID) >=10,999999999,0)
+
SUM({$<Vendor=>}LineSalesAmount)

After I enter this expression, I also make sure that all other sort options are checked off, resulting in the following:

expression_sort 1.3

This matching sheet object dynamically changes depending on the user selections in the vendor list box. Using the status equation for the column colors, I am able to easily separate preferred vendors from all others.

expression_sort 1.4

Comments are closed.