This was originally posted on The Information Lab: https://www.theinformationlab.co.uk/2019/02/21/consultants-diary-nested-lod-for-ranking/
I’m going to try and share some of the techniques and methods I use as a consultant. Using Tableau & Alteryx daily means that I’ve found I take small techniques, tips & tricks for granted. Hopefully this will be useful for you all.
Table calculations can be mysterious.
When first learning about table calculations, it may be a tricky subject to wrap your head around. One of the most used table calculation is the RANK() function. Rank is a function which comes in many flavours…
<figcaption> Yet another reason to pop open that calculation window when you first open a new version of Tableau.</figcaption>
A customer had a very specific question, which they mocked up in the trusty Sample Superstore dataset:
How can I Rank this by Technology (because it has the highest selling subcategory (phones)) - then Furniture - then Office Supplies?
A normal rank function calculates that Phones are the highest, but moving the Basic Rank to the left of Category will rank based on Sub-Category.
So the method I thought of getting around this was using a nested level of detail calculation. The idea behind this was I wanted to create a field which took the highest value/maximum sales value for each Subcategory within each Category.
So I wanted to keep the total sales for each Subcategory, but cascade the maximum value within each Category.
Based on Andy Kriebel’s excellent blog on understanding FIXED LoD calculations, we can translate my solution into plain English.
So with a { FIXED [Category] : MAX ( {INCLUDE [Sub-Category] : SUM([Sales])}) } we are saying…
For each Category, compute the Maximum total sales for each sub-category.
This means that Tableau will first compute and return the values of the SUM(Sales) for each sub-category, and then take the maximum value from this partition for each category.
The FIXED means that the value is returned per row within the category field. This then allows us to use this field with our RANK() table calculation.
<div class="wp-block-file">Ranking_v10.2Download
Feel free to crack open the workbook above to look under the hood of this example.</div>