Note: If an axis using a custom expression with ranking has been set up to Evaluate axis expression on: Current filtering only, then the ranking will be recalculated with each filtering. See Details on Advanced Settings for more information.
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]) |
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") |
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") |
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: