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.
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.
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.
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.
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.
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
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:
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
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.
A review of rank functions in Tableau is here.
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.
ReplyDeleteDear Sir , Why we are using (-Sum(Sales)) . I got the result but not able to understand negative of Aggregated sales
ReplyDeleteThis comment has been removed by the author.
ReplyDelete