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

by Ana Yin

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 trainers in AsiaPac, and as part of the trainer certification process, I had to present a section on Nested Level of Detail (LOD) expressions, 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 allows me to explore not only the ‘golden rule’ of Nested LODs, but also an ‘exception’ to the rule.

So I’m going to divide this topic into 2 parts. This article (Pt 1) will explore the business use case, which Nested LOD expressions work given the context and which don’t work (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’.

Let’s get started on Part 1….

(This article assumes basic knowledge of LODs).

 

The Business Case

As usual, I always start by saying let’s familiarise 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 this:

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

 

SHOW ME

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:

 

Nested LODs 2

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

 

 

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])})}Inner inherits the outer “exclude Country”.

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

 

Nested LODs 4

 

Note: There is an exception to this rule; Part 2 is all about the exception (link will be added at end of article on Wednesday).

 

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!

 

Nested LODs 5

 

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. There are so many reasons.

 

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

 

Nested LODs 6

 

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

 

Nested LODs 7

 

Feeling like a deeper dive?

If your brain isn’t fried as tempura prawn just yet then do continue to Article 2 which goes into detail about the “exception” to the 'golden rule’.

Read on for Part 2. A not so dummy’s guide to Nested LODs. You can find this here.

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 you found this useful!

Share on ...

Industry News, LODs, Tableau Education, Tableau Nested LODs, Technical Articles, Tridant News, Ana Yin, Staff Blogs, Tableau

Alteryx Inspire Conference 2017 Tableau Nested LODs: A Dummy’s Guide (Part 2)

Recent Articles

Blog Categories

See all

Search