Tableau Nested LODs: A Dummy’s Guide (Part 2)

Nesting LODs

Recently, I received the green light to teach Tableau Desktop III (Advanced), a course released late last year.

The week of 19th of Apr 2017 was Train the Trainer week for a few of us in AsiaPac and as part of the trainer certification process, I had to present a section on Nested Level of Detail (LOD) expressions which is one of the chapters of the curriculum.

Nested LODs as I’m sure most of you will agree, is not the lightest topic by any stretch of the imagination. After hours trying to understand the mechanics of Nested LODs, I’ve finally been able to mock up a dataset that allowed me to explore not only ‘golden rule’ of Nested LODs, but also an ‘exception’ to the rule.

Part 2 is all about that exception. If you haven’t already read Part 1, I strongly recommend that you take a look at that first, as this article skips explanations on the dataset. Check it out here

(This article assumes basic knowledge of LODs).

 

The Business Case again….

“What is the most costly Order to ship by Country, for each of the 4 Markets?”

Unlike in Part 1, this time we’ll be answering the question without Country in the level of detail. Here is a view of the desired result:

 

Tableau Nested LODs P2 1

 

The Exception….

The exception to the ‘golden rule’ – inner expression in the nested LOD inherits its dimensionality from the outer expression is when you nest a Fixed dimension inside of another Fixed dimension:

{ FIXED [Country]:MAX({ FIXED [Order ID]:SUM([Shipping Cost])})}

Technically this expression ‘could’ work, given it’s bringing in the two levels of detail that are missing in the view i.e. Country and Order ID. But the result is bizarre, nowhere close to the right result…..

 

Tableau Nested LODs P2 2

 

This time the inner Fixed expression overrides the outer Fixed expression. Let’s take a detailed look….

 

The Inner Mechanics…..

We’re going to take a look at EMEA as it best demonstrates the ‘overriding’ behaviour.

{ FIXED [Country]:MAX({ FIXED [Order ID]:SUM([Shipping Cost])})}

 

Nested LODs eThe expression is saying, for EMEA:

(Bearing in mind we’re already partitioning by Market, so it’s already a level of detail, in the view)

  1. Look for the largest sum of shipping cost for Albania, it’s Order ID 14
  2. Now look for Order ID 14 in ALL countries (there is none, so move onto Algeria)
  3. In Algeria, the largest sum of shipping cost belongs to Order ID 11; does this order exist in other Countries? No, move onto the next Country
  4. In Belarus, largest order is Order ID 17; but this time the same order exists in Canada too, so let’s add that order to the one in Belarus!
  5. In Egypt, largest order is Order ID 15; find the same order in other countries and add their shipping cost on top of the one in Egypt

(567 + 101 + 400 + 450 + 67 + 351 + 67) = 2003

 

Do you see how the Fixed Order ID (inner dimensionality) is now overriding the Fixed Country (outer dimensionality)?

To make matters even more confusing, in a way we are still ‘fixing’ by country, because we are scanning for Order IDs in each Country within the Market.

 

So what’s the fix?

In this context, the correct result doesn’t even need a nested expression! It is as simple as just including the two missing levels of detail into the equation and taking the maximum sum of shipping cost:

MAX({ INCLUDE [Country], [Order ID]:SUM([Shipping Cost])})

 

Tableau Nested LODs P2 4

 

If you would like to have the packaged workbook to have a play yourself (and save yourself the hassle of typing in the data), please email us at info@tridant.com and we will give you access.

I hope this was helpful!

THE END.

 

Share on ...

Ana Yin

Senior Consultant

ana.yin@tridant.com.au

1300 737 141

View more articles by Ana Yin

Leave a comment