Tableau Nested LODs - A Dummy’s Guide

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.

So I’m going to divide this topic into 2 parts. Part 1 of this article will explore the business use case, which Nested LOD expressions work given the context and which don’t work and more importantly, ‘why’ it doesn’t it work). Part 2 is more of a deeper dive and will elaborate on the exception to the Nested LOD ‘rule’.

Note! This article assumes basic knowledge of LODs.

Part 1: Tableau Nested LODs - A Dummy's Guide 

The business case

As usual I always start by saying let’s familiarize ourselves with the dataset.

Here I have a bunch of Orders split out by Country and Market. The question you’re trying to answer is:

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

Where do I start? 

The starting point is to assess what ‘level of detail’ is missing from the view that I’ll need to consider, in order to answer the question – Order ID. Reiterating the question:

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

We know we need to factor in Order ID, so let’s try an Include expression:

before switching the aggregation from SUM to MAX:

I like the fact that this is ‘partially’ correct as I see the right numbers, but it doesn’t fully address the question, that is to retrieve one order per Market, not per Country. So how do we get the same result for all countries within each Market?

The nesting begins

Perhaps through an Exclude expression as well? This is where the nesting begins.

Great, we get the same result, but something’s off with APAC; why didn’t the expression return 682 and instead returned 736?

 The answer, as well as the “golden rule” (note below) is that with Nested LODs, the inner expression in the nested LOD inherits its dimensionality from the outer expression. This means that:

Is really saying “Factor in Order ID but at the same time exclude Country from the equation, thus it Order ID overrides Country and the duplicate ID of #1 is summing up (682 + 54 = 736).

Note! There is however an exception to this rule: Part 2 of this article is all about the exception.

What’s the fix?

Spoon feed the equation a bit more by forcing it to factor in Country in order to address the duplicate Order ID issue.

And this time we’re in action!

You can see why I’ve deliberately thrown in a cross-country duplicate Order ID into the dataset, purely to illustrate what could go ‘wrong’ in nesting. Students in the past have asked “why would different countries have the same Order ID?” It could be that the data pertains to a global retailing corporation that stocks several countries at one time, so many reasons.

Are there alternative ways?

Of course there is! There almost always is more than one way of arriving at the same result.

We could have easily achieved the same result using a Fixed expression on Market:

But why doesn’t this work?

Technically this expression ‘should’ work, after all I’m fixing by Market so I don’t get different shipping costs per Country, and my Country is already a level of detail in the view so surely I don’t need to include it again right?

But remember the ‘golden rule’ - inner expression in the nested LOD inherits its dimensionality from the outer expression. Because we’ve inherited the ‘fixing’ of Market, Tableau has chosen to disregard the Country level of detail and sum up that duplicate ID of #1 in Thailand and the Philippines (54 + 682 = 736)

Essentially the above expression is identical to:

However you can easily fix it with this expression to get the right result:

Need a deeper dive? If your brain isn’t fried as tempura prawn yet, then do continue to Part 2 which goes into detail about the “exception” to the ’golden rule’.

Part 2: Tableau Nested LODs - A Not-So Dummy's Guide 

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.

Note! 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.

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:

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.

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.

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:

See original articles, in full, here and here.

Simplify your data prep and analysis to make critical decisions fast.
Request a demo to learn how Tridant helps clients transform their data to answer mission-critical business questions.
Ana Yin | Michelle Susay