## Understanding Level of Detail Expressions – Part 1

[Before I get started with this post I wanted to recognise my colleague, Meredith Dicks, who had the initial idea for these LOD expression diagrams. Credit for the initial concept goes to him… I just refined the pictures.]

At Tableau our goal is to make the act of data analysis a delightful experience. Our users often tell us that when they are deeply engaged in Tableau they stop thinking about the mechanics of using the product and just have fun asking questions of their data. We call this experience flow – a state of joyful immersion in a task.

Sometimes though you encounter a question that is simple to articulate but when you try to answer it in Tableau you find it to be unexpectedly hard. Suddenly the state of flow is broken and you need to start thinking about how to solve the problem rather than focusing on the question and answer. This is jarring and can be frustrating for new users as they may not know how to proceed.

At the heart of all these problems is the need to work with data that has been aggregated to different levels. For example, to compare the average order size across sales regions with the worldwide average order size you need to calculate a value:

• At the level of the order (i.e. you need to sum the individual line item values);
• At the level of the region (i.e. take the total order value and average it by region); and
• At the worldwide level (i.e. take the total order value and average it across all orders).

In Tableau we refer to the “level of detail” (abbreviated to LOD). This is essentially how aggregated or granular the data is and it is determined by the dimensions in the viz. By default, Tableau will aggregate the data LOD to match the Viz LOD:

Prior to Tableau 9 working with data at different levels of detail could be hard. Table calculations were one way to roll data up to a higher level of aggregation and there were other clever techniques using sets and blending, but they only worked for certain problem types and required you to think about the mechanics of the solution rather than keeping in the flow.

In Tableau 9 we are introducing a new feature called “level of detail expressions”. These allow you to directly define the level of detail at which a calculation should be performed which in turn removes the need for you to think about convoluted workarounds. They look like this:

{ FIXED [Order ID] : SUM([Sales]) }

The blue part of the expression should be familiar – it’s just an aggregation function that tells Tableau to calculate the sum of Sales. The red part is the new capability and it tells Tableau what level of detail to use when performing the aggregation. In this case, it tells Tableau to perform the aggregation for each Order ID, irrespective of the dimensions used in the viz.

There are three types of LOD expression – INCLUDE, EXCLUDE and FIXED – and we will explore them in more detail below.

INCLUDE

If you want to calculate an expression at a level that is (less aggregated than/more granular than/below) the Viz LOD, use the “INCLUDE” keyword. This adds the dimension(s) specified to the Viz LOD. As the result is (less aggregated/more granular) than the Viz LOD the value needs to be aggregated for each Viz LOD record (e.g. sum, avg, min, max, …).

EXCLUDE

If you want to calculate a result at a level (more aggregated than/less granular than/above) the Viz LOD, use the “EXCLUDE” keyword. This removes the dimension(s) specified from the Viz LOD. As the result is (more aggregated/less granular) than the Viz LOD the value needs to be duplicated for each Viz LOD record.

FIXED

The “FIXED” keyword allows you to specify the aggregation/granularity independently to the dimensions used in the Viz LOD. If the result is (more aggregated than/less granular than) or unrelated to the Viz LOD then it is duplicated for each Viz LOD record.

If the result is (less aggregated than/more granular than) the Viz LOD then it is aggregated to match.

Nested LOD Expressions

It’s also possible to create calculations that have multiple LOD expressions nested inside each other. For example, you could start with the viz LOD, then have an inner part that uses an INCLUDE expression to produce a more granular result. This is then wrapped in an EXCLUDE expression so the inner result is aggregated to the outer LOD. Finally, the calculation LOD is resolved back to match the LOD of the viz.

In the next post I’ll show how LOD expressions relate to the techniques we used to solve these types of problems in Tableau 8 and earlier.

Hi. I'm Alan. By day I manage the APAC sales engineering team for Snowflake Computing. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

### 15 Responses to Understanding Level of Detail Expressions – Part 1

1. mluttonBI says:

This is a wonderful resource — thank you Alan!

2. Rayolight says:

Reblogged this on Ray O Light's Insights and commented:
What a crisp clear way of illustrating LOD of Tableau 9. Nested LOD really opens up a whole new world of possibilities in calculations!

3. Jagjit says:

Thanks Alan. Does this mean we no longer have to use custom SQL queries to aggregate data before bringing to Tableau?

• Not necessarily. LOD expressions may remove the need for custom SQL in some cases but there will still be times when it is required e.g. if you have complex multi-level aggregations.

4. Srini says:

Thanks for sharing the insights Alan ! Much appreciated…

5. Ravi Kulkarni says:

Hi Alan,
I was wondering can we use LOD calculations to show, last 4 same day averages?
Consider an example where we are only showing Today’s data [Monday 16th Nov] and we would like to show last 4 Monday averages for various dimensions we have in visualization. Typical window functions won’t work, because last 4 Monday’s data is not in the visualization. At present we have to prepare data in that fashion before we extract in tableau. But Iw as wondering, if in out extract we have data for say last 6 months, how we can compute L4 averages on fly using LOD?

Thanks for great work.

6. Anh-Vi Hoang says:

Alan, I highly appreciate “Understanding Level of Detail Expressions” white paper. It’s very useful and helpful in resolving some data blending issues.
However I couldn’t find the same Superstore database sample that you used. The ones that I downloaded either has US only in country. I was able to find an international version but it’s not the same (the data is different, there’s no [Sales Rep], etc.). Please help me find the right Superstore database sample.

Thank you very much,

Anh-Vi Hoang

• Hi Anh-Vi,

I’m glad you find the whitepaper useful. That specific example you refer to was not done using a standard Superstore – it’s something one of my colleagues mocked up. You should be able to perform the same calculation type by substituting [Customer Name] instead of [Sales Rep].
I hope this helps.

Cheers,
Alan

7. David Dumas says:

Alan,
Is it possible to do this: “Show me sales by customer for customers that bought network hardware last year?” Assume that “network hardware” is an item category.
Is it possible to do this: “Show me sales by customers for customers that are also vendors?” Assume that sales and puchases share a common customerid.
Thanks,
Dave

• Hi David,

Yes to both.

1. You can do this with a simple calculation that isolates the specific transactions that meet your criteria.

2. There are several ways to do this – one way is (if your data source is a relational DB) to use a RAWSQL query statement to work out if the buyer is also a seller.

I’ve created an example workbook you can download and look at the two solutions. Snag it from my dropbox:

https://www.dropbox.com/s/5by9tae6ta7omii/Example%20Solutions.twbx?dl=0

Hope this helps,
Alan