26 May 2016

LOD Expressions: How to Use the 'Include' Expression on Tableau

Tim Day

To find out more about Concentra's bespoke Analytics Services and Solutions see Services.

Level of Detail (LOD) expressions can be difficult to understand for people that use Tableau. There are three types of LODs: Fixed, Include, and Exclude. In this blog, I will focus on the Include expression.

What is LOD

LOD is simply the level of aggregation shown in the view. If you have Profit vs. Sales split by Year and Region, the Year and Region is your Level of Detail. If you want to know more about LOD's, read our blog: A Quick Guide to Tableau Level of Detail Expressions.

When to use each LOD

LOD's are used to show something at a different Level of Detail to the current view. This can be either a lower or higher level of aggregation.

For Fixed expression, you specify what level of aggregation occurs; for Exclude you remove part of the LOD to get a higher level of view (more aggregated) and for Include you add an extra part to the LOD to get a lower level of view (less aggregated). 

The image below shows which Level of Detail expression is required to get to a certain granularity on the page. This does not include Fixed, as it is not affected in any way by any drill down/up functionality on the page.

Tableau Level of Detail Expression

figure 1: showing how the LOD expressions affect granularity

how to use the include expression

The Include LOD calculates the aggregation at the level of detail specified by the dimensions in the view and the dimensions included in the calculated field. This will potentially result in the calculation of the aggregation at a lower level of detail than the view. This is the bottom half of Figure 1.

Now that you know the basics of LOD expressions, we can move on to learning exactly how to add an Include LOD expression into Tableau.

We will start by connecting our Tableau workbook to the EU Superstore data (You will be able to follow along with any of the superstore data). We will then make a simple table of the Sales split by Region, Category, and Sub-Category. This should look similar to my table below - don't worry if the values are different. We can now create the calculated field for the lowest Sales for each order.

Tableau Level of Detail Expression

figure 2: showing the start of our view

Next step is to write { INCLUDE [Order ID] [Order Date] : MIN ([Sales]) }. This means we are finding the lowest Sale for each unique Order ID and Order Date. If we add this to the chart and change the default SUM() to MIN() then we will find the single smallest order for each Region, Category, and Sub-Category. A minor bit of formatting gets us the chart shown below.

Tableau Level of Detail Expression

figure 3: The completed view with lod expression

While this is a very simple version of a LOD expression (and there are far easier ways to get the same answer), it does show exactly how you could build it up for a more complex situation. The most important thing to remember about LOD's is not to panic when it comes up, just figure out at what level you would like the aggregation to occur at and then decide if it should be fixed or variable to the LOD on display.

Often people struggle with identifying where a LOD needs to be used. This is when a question is asked of a question. A more complex example that can be very quickly solved with LOD expressions would be "Which Country has the highest average performing Sales Person where the performance of the Sales Person is based on their largest closed Sale?".

This sounds complicated, but only one LOD expression is needed to solve it. You should be able to see that the LOD of aggregation is the Sales Person, and we want to find their maximum sales. This then needs to be averaged at the Country level to complete our expression. Putting this together gives Avg{ Include [Sales Rep] : Max ( [Sales]). This calculation can now be used anywhere in the workbook and by adding another field such as Year we can find even more data with minimum extra work.

Tim Day

About the author

Tim Day is a Graduate BI Developer at Concentra. After graduating from Warwick with a bachelor's degree in Mechanical Engineering, he wanted to continue working where he would have the freedom to create something visually appealing. Having started at Concentra as an intern, he loves the data visualisation part of the job and has also grown to love the data warehousing side. He especially likes providing insights into sports events, as this data is often not shown in a captivating way.

Related to LOD Expressions: How to Use the 'Include' Expression on Tableau