In Tableau Prep, RANK is the analytical function that assigns a rank or position to each row of data based on certain criteria. This can be useful when you want to analyze or visualize data in a way that highlights the relative position of each data point within a specified order. Or when you simply what to sort the data in Tableau Prep!
This blog post will go through the different ways you can use RANK in Tableau Prep, using the following sample data of customers and profit:
Rank by profit
Say we want to see our overall top 5 customers by profit. The steps for this would be as follows:
- On the profit column, navigate to Rank, as shown:
- In the dialog box that pops up, we see that profit is automatically sorted in descending order, as below. If we'd like to change the order to ascending, we'd simply click on the sort order icon to flip it.
- On the top left, rename the rank from 'Calculation1' to 'Rank by Profit':
- Select Done. This is the default Rank function in Tableau Prep. It assigns a whole number rank starting with 1, in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value. The number of rows with the same rank is added when calculating the rank for the next row, so you may not get consecutive rank values. This means that when there's a tie, as in our case for the first two customers, the next rank number is #3 as opposed to #2.
- Then, to filter to the top 5, simply apply the Filter to the 'Rank by Profit' column, and select the first 5.
Rank by profit, for every region
If we want the rank to re-start every region, this is how we would set up the rank function:
- In the drop-down menu under Group By, select 'Region'
- Select Done. And we see on the data pane that our rank restarts on every region:
Rank by profit, for every region, and order customer names alphabetically
In cases where there are ties (i.e., multiple customers with the same profit), we have options for handling ties. For instance, we can user Order By to rank the customer names alphabetically.
Order By generates a new sequence for the rank, so that where there are ties, what's now defined helps break the ties. In our example, notice that the first 2 customers with the same profit in the Central region are no longer tied at Rank #1, but are now split into #1 and #2, as the alphabetical order breaks the tie.
Variants of Rank
At this point, you may have noticed that there are different variants of Rank when you click on the dropdown arrow next to the Rank:
What this blog has covered so far is the default Rank. The other two are:
Dense Rank: Assigns a whole number rank starting with 1 in ascending or descending order to each row. If rows have the same value, they share the rank that is assigned to the first instance of the value, but no rank values are skipped so you will see consecutive rank values.
Percentile Rank: Assigns a percentile rank from 0 to 1 in ascending or descending order to each row.
The following example shows a comparison of each of the above Rank functions applied to the same data set:
