Ranking functions
The list shows the ranking functions that you can use in expressions.
| Function | Description |
|---|---|
| DenseRank(Arg1, Arg2, Arg3...) | Returns an integer value ranking of the values in the selected column. The first argument is the column to be ranked.
An optional argument is a string determining whether to use an ascending (default) or a descending ranking. For the highest value to retrieve rank 1, use the argument "desc", for the lowest value to retrieve rank 1, use "asc". Ties are given the same rank value and the highest ranking number equals the number of unique values in the column. Additional column arguments (optional) can be used when the column should be split into separately ranked categories. Examples: DenseRank([Sales]) DenseRank([Sales], "desc", [Region]) For an example, see Ranking. |
| Rank(Arg1, Arg2, Arg3...) | Returns an integer value ranking of the values in the selected column. The first argument is the column to be ranked.
An optional argument is a string determining whether to use an ascending (default) or a descending ranking. For the highest value to retrieve rank 1, use the argument "desc", for the lowest value to retrieve rank 1, use "asc". Ties are given rank values depending on optional argument values: "ties.method=minimum" (default), "ties.method=maximum", or "ties.method=first". See More about ranking ties below for more information about the available arguments. Additional column arguments (optional) can be used when the column should be split into separately ranked categories. Examples: Rank([Sales]) Rank([Sales], "desc", [Region]) Rank([Sales], "ties.method=first") For an example, see Ranking. |
| RankReal(Arg1, Arg2, Arg3...) | Returns a real value ranking of the values in the selected column. The first argument is the column to be ranked.
An optional argument is a string determining whether to use an ascending (default) or a descending ranking. For the highest value to retrieve rank 1, use the argument "desc", for the lowest value to retrieve rank 1, use "asc". Ties are given rank values depending on optional argument values: "ties.method=minimum" (default), "ties.method=maximum", "ties.method=first", or "ties.method=average". See More about ranking ties below for more information about the available arguments. The average ties method is used when calculating data relationships using Spearman R. Additional column arguments (optional) can be used when the column should be split into separately ranked categories. Examples: RankReal([Sales]) RankReal([Sales], "desc", [Region]) RankReal([Sales], "ties.method=average") |
More about ranking ties:
With the functions Rank and RankReal, you can add an optional ties method argument depending how you want equal values to be ranked.
| Argument | Description |
| "ties.method=minimum" | Gives all ties the smallest rank value of the tie values. |
| "ties.method=maximum" | Gives all ties the largest rank value of the tie values. |
| "ties.method=first" | Gives the first found tie value the lowest rank value, and continues with the following rank value for the next tie. |
| "ties.method=average" | Gives all ties the average of the rank values for all ties. |
Example:
When a list is to be ranked, its values are first of all sorted. Then, the sorted values are assigned a rank value depending on the order in the sorted list. What rank is given to a tie value depends on the ties method. Empty values are left empty and do not receive any rank.
| List of values | Rank with "ties.method=minimum" | Rank with "ties.method=maximum" | Rank with "ties.method=first" | Rank with "ties.method=average" |
| 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 3 | 2 | 2.5 |
| 3 | 4 | 4 | 4 | 4 |
| 2 | 2 | 3 | 3 | 2.5 |
| (Empty) | (Empty) | (Empty) | (Empty) | (Empty) |
| 5 | 5 | 5 | 5 | 5 |
If DenseRank was used, the resulting rank values in the example would be 1 2 3 4.
See also Functions.