Tableau Nested LODs - A Dummy’s Guide

Published on: 21 February 2021
Written by: Tridant

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?”

Tableau Nested LODs

Self Check: Most costly orders (sum of shipping cost) within each Market boxed in purple, to make sure we all understand the question.
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:

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

before switching the aggregation from SUM to MAX:

Tableau Nested LODs2

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.

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

Tableau Nested LODs3

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:

{ EXCLUDE [Country]:MAX({ INCLUDE [Order ID]:SUM([Shipping Cost])})}
Note: Inner inherits the outer “exclude Country”

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).

Tableau Nested LODs4

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.

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

And this time we’re in action!

Tableau Nested LODs5

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:

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

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?

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

Tableau Nested LODs6

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:

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

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

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

Tableau Nested LODs7

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.

Tableau Nested LODs8

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 LODs9

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])})}

Tableau Nested LODs10

The 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])})

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 

Related Articles

Copyright © Tridant Pty Ltd.

Privacy Policy