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

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

Ana Yin

Senior Consultant

ana.yin@tridant.com.au

1300 737 141

View more articles by Ana Yin

4 thoughts on “Tableau Nested LODs: A Dummy’s Guide (Part 1)

  1. Great job Ana Yin. I remember 2 years ago we were whiteboarding at The Nielson Company and wrote on the board two nested LOD calcs,.. In my brain I figured, okay if it can do this, we are definitely in the clear, and someone paused the meeting and said, “wait can Tableau do that?”

    And I replied, “It better, or we are going to be doing a lol more SQL!!” Luckily it worked!
    boraberan(.)wordpress(.)com – a old friend of mine and also one of the product managers around LOD calcs has some really great intro blogs that helped us wrap our head around it back at Tableau Software (when I worked there).

    Hopefully leaving it here in the comments helps your end users with the beginning phases, built by the originator of the wiki!

    This is such a great direction to start publicizing to the Tableau Desktop Community, great work Ana Yin. I will be sure to follow your content moving forward. Thanks for your time.

    Thanks!
    TylerG

    • Thanks for the feedback Tyler! Agreed – it’s a very powerful (and under-used) feature in Tableau. And thanks for pointing out intro blogs for those who need to wrap their head around the fundamentals of LODs first, much appreciated!

      Ana

Leave a comment