Search code examples
spotfire

Detailed Explanation for Dense Rank Explanation

I am trying to understand Dense rank function when multiple arguments were uses. Could some one help me in understanding with the below example or any other example?

Thanks a ton in Advance!

Calculated Column: DenseRank([Country],[Event Identifier])

Screenshot

Solution

  • From the Help Pages

    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])

    So, in your example you are ranking the Country grouped by / partitioned by the Even Identifier with a default "asc" order. This is done alphabetically, thus, if we look at Interim 1 we will see 4 ranks, 1-4 since you have 4 countries for Interim 1, in alphabetical order (ascending). Each "group", which is the 3rd argument and in your case Event Identifier, will get a set of rankings from 1 - n where n is the number of distinct values. If you remove this argument, the entire data set will be ranked without consideration of Event Identifier