In Tableau, you have the option of ordering your data by Rank. This will list your data in descending or ascending order, giving the highest or lowest values, respectively, the rank of one. Every number that follows will be ranked according to its order.
Tableau offers several ranking options:
- RANK
- RANK_DENSE
- RANK_MODIFIED
- RANK_UNIQUE
- RANK_PERCENTILE
In this blog, I will discuss the difference between ranking by RANK and RANK_DENSE.
To start, take the Count Distinct of Order ID from Tableau’s Superstore dataset and group them by State/Province. Then, sort the results in descending order.
You will have a list that looks like this:
California is first on this list. Now, we have the option of ranking the states using any of the ranking functions listed above. Here is an example of what the list looks like when ordered by both RANK and RANK_DENSE:
For the first 14 rows, the rankings appear to be identical. However, on row 15, RANK assigns Colorado a rank of 15, while RANK_DENSE assigns Colorado a rank of 14.
The reason for this difference lies in how these two functions operate. Tennessee and Georgia are tied for rank 13 with a count of 91. Both RANK and RANK_DENSE assign them the same rank of 13. However, RANK skips the next position and assigns Colorado a rank of 15, while RANK_DENSE continues sequentially and assigns Colorado a rank of 14.
In Tableau, both RANK and RANK_DENSE functions are used to order data based on a chosen measure, but they handle ties differently. When two or more values are tied, RANK assigns the same rank to each tied value and then skips the next rank number(s), leaving gaps in the sequence. RANK_DENSE, on the other hand, assigns the same rank to tied values but continues the ranking sequence without skipping any numbers.
In other words, RANK produces rankings with possible gaps, while RANK_DENSE produces a continuous sequence of ranks. Choosing between them depends on whether you want to preserve the gaps caused by ties or maintain a consecutive ranking order.