Rank and Ranking in Tableau Prep

• By Joshua Milligan
• Posted in All, Calculations, Data Blending & Joining, Data Shape & Structure, Tableau Prep, Tips and Tricks
• On June 15, 2018
• 16 Comments.

One thing I’ve submitted  an idea for Tableau Prep (and would love if you voted for it!) is the ability to use Table Calculations, especially Index() and PreviousValue().  This would solve a lot of use cases, including adding row numbers, rank, sort, handling cumulative values, and more.  But to compute a rank or sort specifically, there is a work-around that will work in certain cases. Here a possibility for sorting and ranking in Tableau Prep:

Ranking in Tableau

Let’s use the familiar Superstore Sample data set which has Sales for Orders made by Customers in various States.  Let’s say I want to rank the customers for each state based on the amount of sales.  Using Tableau, I have the option to use the Rank() table calculation, and it isn’t too difficult to come up with something like this:

The difficulty with table calculations is 1) you have to set the right addressing and partitioning (not too bad in this case) and 2) you have already gone down several layers of complexity, so deeper analysis is going to be even more complex.  What if you could rank the source?

Ranking in Tableau Prep

Let’s take the same data set and see how we might achieve ranking in Tableau Prep.  If I want to rank by customer within state, here are the steps I might take:

1. Create an aggregate of Sales by State and Customer:
   
2. Add a Step after the aggregation that contains the aggregate rows:
   
3. Join the Aggregate Rows to the previous aggregate step.  For all intents and purposes, I’m joining the aggregation to itself:
   You’ll notice that I kept it as an inner join, matched on State (to ensure the ranking is done within the state) and then also included a condition that the Sales of the State & Customer must be less than or equal to the Sales of the Aggregate Rows step.  In other words, I want to match records where the state is the same and where a customer had higher (or the same) sales.  That means if Customer A had the highest sales, they would only match on themselves (and if we count the number of records, we’d get “1” – Rank #1 – ranking in Tableau Prep – but now we’re getting ahead of ourselves).
4. Add an Aggregation to the Join that allows us to count the Number of Records per State and Customer (okay, we weren’t too far ahead of ourselves):
   The Number of Records is the number of matches from the join which ultimately is the Rank of the customer within the State.  So, we’ll…
5. Add a clean step to rename the Number of Records to Rank Within State:
   
6. Finally, we can Join that Clean step to the Original set of records on Customer and State.  Our final output contains all the original records with the addition of the sales Rank Within State!
   

And now, we have something that makes even complex analysis in Tableau so much easier!  Do I want to compare summer sales of my top 10 customers per state?  Easy!  Do I want to look at customers who are top in one state and bottom in another? Easy!  There’s no end to the analysis that suddenly became easy because I moved the complexity to the data prep (and Tableau Prep made that easy too! In fact you can download the flow here!)