Thursday, March 12, 2015

Nested Sorting and Top N per Category via Rank_Unique

This task can be implemented using Index() as documented by many. Here I am going to present an alternative solution based on Rank_Unique(). I found it a little easier than using Index().

Let's use the classic Superstore example. We want to get Top N Categories per Department in terms of Sales.

Nested Sorting

Step 1.
Drag Department and Category to the Row shelf. 

Step2.
Create a calculated field Rank_Unique: Rank_Unique(Sum([Sales])) . 

Step 3.
Drag it to the Row shelf and turn it into Discrete. Move it to between Department and Category.

Step 4.
Right click on Rank_Unique and select Compute Using>Category
Now, the Nested Sorting is achieved without [Sales] in view! Yeah. We don't really need it if all we want is the ranking of categories per department.

Top N Filter
Next is to set up the Top N filter. 

Step 5.
Drag the green Continuous pill Rank_Unique to the Filter shelf. Click OK when the filter settings pops up.

Step 6.
Repeat Step 4, for the filter pill. Select At Most for the filter settings when prompted again.
Step 7.
Right click Rank_Unique and show quick filter. And we have a sliding bar for Top N.
Voila, we are done!

An alternative is creating a Rank_Unique<=N filter. I will leave that as an exercise.

Dress Up

The above is the minimum barebone version of nested sorting and top N per category viz. To dress it up, we may need to do the following.

- To see the visuals and values, drag [Sales] to both Column shelf and Label shelf. 
- Hide the rankings by unchecking Show Header under Rank_Unique
- Edit the filter title to be Top N.
- Drag Department to Color Shelf to add some color.

The workbook can be downloaded here.

Bottom N

Given that we know how to get the top N per category, we only need to replace
Rank_Unique(Sum(Sales)) 
by 
Rank_Unique(-Sum(Sales)) 
Following the same steps, we get the bottom N per category. Make sure the table calculation is always Compute Using>Category.

Both Top and Bottom N

Create a filter as follows:
Rank_Unique(Sum(Sales)) <=N or Rank_Unique(-Sum(Sales)) <=N

Drag it to the filter shelf and fix the table calculation. Then it's done.
A more detailed post on this can be found here
One Line of Code to Get Both Top N & Bottom N

Why not Rank()? 

Rank() function produces non-distinct rankings as well as Rank_Modified() and Rank_Dense() do. Thus sometimes we may not get exactly N elements using a Rank()<=N filter. Rank_Unique() returns distinct rankings even for identical values. If you expect 10 elements consistently from a Top 10 filter, instead of 9 or 11, Rank_Unique is the way to go.

A review of rank functions in Tableau is here.


3 comments:

  1. Good tutorial and some great info! I would say that Rank() and it's various flavors are useful in many cases, so I wouldn't discount them -- but if you want exactly N items, then Rank_Unique() is the way to go.

    ReplyDelete
  2. Dear Sir , Why we are using (-Sum(Sales)) . I got the result but not able to understand negative of Aggregated sales

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete