Qlik Sense Ranking Functions – Rank and HRank Function

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense inter-record functions. Today, we will see Qlik Sense Ranking Functions. Moreover, we will see the use of ranking functions in the chart expressions. These Qlik Sense Ranking Functions are used to rank the row value comparing them with the other values in other rows of the table. Such evaluations are important in conducting a comparative analysis of values.

Qlik Sense Ranking Functions – Rank and HRank Function

So, let’s start the Qlik Sense Ranking Functions tutorial.

2. Qlik Sense Ranking Functions

i. Rank() function

The rank() function is used to rank the values in a dimension contained in rows. Values in each row are evaluated and compared with values in the other rows within the same dimension or field. So, after the comparison of values in the rows, the rows or corresponding values are ranked on the basis of how big or small the value is as compared to other values. For example, 100 will be ranked 1st and 95 will be ranked 2nd and so on. This function returns a value in dual format i.e. in both text and numeric representation.

Recommended Reading – Qlik Sense Treemap Visualization

Syntax:

Rank([TOTAL] expr[, mode[, fmt]])

Where, expr is the dimension or field whose rows or values you want to sort and rank.

Mode sets the number representation of the rank returned by the function. You can set this parameter in several ways such as,

0 which is the default value and assigns a rank to values depending on which side of the middle value they fall on. Basically, if a value falls on the lower side of the middle value then it returns the lowest rank number. And if the value falls on the higher side of the middle value then the function returns the highest rank value.

Other values can also be set like 1 (lowest rank), 2 (average rank), 3 (highest rank on all rows) and 4 (for lowest rank on the first row then one rank incremented for each new value).

Fmt sets the text representation of the rank returned by the function. Several values can be set for this parameter like, 0 which is the default value signifying a range of low value- high value. 1 for low value on all rows and 2 for low value on the first row then blank on all the other.

The TOTAL prefix or qualifier if used evaluates all the columns or dimensions of a table and included only those values in evaluation which are the same as the value it is evaluating in the current row.

For instance, we have a data record with fields like Customer and Sales. The rows in the fields have sales values for respective customers. Using the rank function Rank(Sales) we are going to create a measure field in the table which will be ranking of sales values either sorted on Customer or on Sales.

So, as can be seen in the table given below, the values are sorted on the basis of Customer and so all the values corresponding to Benedict will be evaluated and ranked first. The 1st rank is given to the highest value, 88. The ranks descend with the decrease in the value. So, after evaluation of Benedict’s values is complete, then Ethan is taken up for evaluation. The lowest value associate to Ethan is 12 and hence, the lowest rank 10 is given to it.

You must read – Qlik Sense Pie Chart

If, the table is sorted on Sales, then for any sales value which is same for two customers, the rank would be 1-2 and for those values for which the sales value is unique, the rank is 1.

As you can see, when a sales value, say, 12, is common between two customers Benedict and Ethan, then the rank is given as 1-2. And when the sales value 56 is which is common between three customers, Benedict, Joshua, and Ethan, then rank is 1-3.

Do you know about Qlik Sense Mathematical Functions

ii. HRank() function

The HRank() function is a function exclusively used for pivot tables and returns NULL if used in any other visualization. This function evaluates a value given in a row of a column in a pivot table and ranks it according to the values within the same row segment in other columns. So, the result will be rank of the currently evaluated value amongst the entire row segments (i.e. evaluating values of a row horizontally).

Syntax:

HRank([ TOTAL ] expr [ , mode [, fmt ] ])

Where, expr is the dimension or field whose rows or values you want to sort and rank.

Mode sets the number representation of the rank returned by the function. You can set this parameter in several ways such as,

0 which is the default value and assigns a rank to values depending on which side of the middle value they fall on. If a value falls on the lower side of the middle value then it returns the lowest rank number. And if the value falls on the higher side of the middle value then the function returns the highest rank value.

Other values can also be set like 1 (lowest rank), 2 (average rank), 3 (highest rank on all rows) and 4 (for lowest rank on the first row then one rank incremented for each new value).

Fmt sets the text representation of the rank returned by the function. Several values can be set for this parameter like, 0 which is the default value signifying a range of low value- high value. 1 for low value on all rows and 2 for low value on the first row then blank on all the other.

So, the TOTAL prefix or qualifier if used evaluates all the columns or dimensions of a table and included only those values in evaluation which are the same as the value it is evaluating in the current row.

For example, if we have sales values of different customers, we can rank them on the basis of their performance (total sales are done).

We have made the four customer names in vertical order (like in a crosstable) but in the pivot table, all four were part of the same row, occurring horizontally.

Let’s revise Qlik Sense Table Functions

So, this was all in Qlik Sense Ranking Functions. Hope you like our explanation.

3. Conclusion

Hence, we saw all the Qlik Sense Ranking Functions. Thus, these were two of the most important rank functions used in Qlik Sense for ranking values either in rows or columns. You can regulate how the functions will work by defining certain parameters in the functions. These functions are only used in the chart expressions and not in the data load script.

Still, if you have any query regarding Qlik Sense ranking Functions, ask in the comment tab.

See also – 

Create Applications in Qlik Sense 

Reference for Qlik Sense