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