Let's celebrate Level Of Detail calculations!

Let’s celebrate Level of Detail calculations!

During the Devs at Desk session at the virtual Tableau Conference 2020, Filippos Lymperopoulo - an analytics product manager - introduced Quick Level of Detail (LOD) calculations to the Tableau community. This upcoming feature allows users of different levels of ability to create LOD calculations within their workflow, by drag-and-dropping a measure, or by using the context menu to quickly customize the calculation.

This new feature immediately piqued my interest. I remember feeling intimidated when I first started to learn about LODs. Everyone around me framed them as difficult, while also emphasising the importance of understanding the concept. It took me a while to understand how to implement them. I believe the Quick LODs feature will open up new opportunities, and make it less daunting for new users. So to celebrate this upcoming feature, I want to dedicate today's Biztory blog to LODs and analyse four use cases.

Before we start analysing, let’s quickly summarise what LOD expressions are. LODs permit you to compute values at the visualisation level and at the data source level. They also give you even more control on the level of granularity you want to compute. LODs can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED). If you have not worked with LODs before, or would like a refresher, please read some useful articles that helped me when I was learning about LODs:

• Create level of details calculations in Tableau

• Introduction to level of detail expressions

• Fixed level of detail expressions

Let's move on to the fun part. I used the famous Superstore data source to create the examples based on my own experiences. Here are four use cases where an LOD calculation can make your analysis easier.

Duplicate Records

Imagine you're aggregating a measure, and you notice the result is different from what you expect. After some investigation, you realize you're dealing with duplicate data, which is caused by a join. Deduplicating data caused by joins is a common use case for LODs. In my example, I'm joining the Orders and Returns tables. However, this causes the total sales amount to be incorrect because of duplicated records. We can solve this by adding an LOD calculation.

Screenshot 1: Duplicated Total Sales Amount

To solve this a LOD calculation needs to be implemented. Steps you need to take:

1. Calculated field
   
   //Disparate Sales

   {FIXED [Row ID]: MIN ([SALES])}

    

2. Set aggregation to sum

Screenshot 1.2: Disparate Total Sales Amount

Synchronising chart axes

If you want to showcase multiple charts within one dashboard, it can happen that the axes are not synchronised. Imagine you are displaying sales for the Central and East region. At first glance, the line charts look good, but the axes are not synchronised. This could lead to misinterpretation by your audience, and them making an unfair comparison.

Screenshot 2.1: Dashboard without synchronised axes

Using an LOD permits you to fix the axes to a specific maximum that automatically changes when the sales amount eventually exceeds the maximum you have fixed. The LOD uses a reference line to create an adjusted fixed axis.

Steps you need to take:

1. Calculated field:
   
   //Regional sales by month

   {FIXED [Region], DATETRUNC ('month', [Order Date]): SUM ([Sales])}

    

2. Calculated field:
   
   

   //Maximum Regional Sales by month

   {FIXED: MAX ([Region sales by month])}

    

3. Drop Maximum Regional Sales by Month on the detail on the marks card.

4. Create a reference line. Choose as a value the Maximum Regional Sales by Month with a Maximum aggregation. No label, tooltip, and line.

Screenshot 2.2: Reference line

Using this method is better than fixing the axes, because the calculated field will update as soon as the data changes. This method permits you to automate your work and not manually update the axes. I love automation 😀

Screenshot 2.3: Dashboard with synchronised axes

Creating bins on aggregated data

LOD calculations permits you to easily create bins on aggregated data. You may want to analyse if there are seasonal effects and know your profit per day. To do this you need to measure the number of profitable days within each year or month.

Steps you need to take:

1. Calculated field:
   
   

   //Profit Per Day

   {FIXED [Order Date]: SUM ([Profit])}

    

2. Calculated field:
   
   

   //Daily Profit Key Performance Indicator

   IF [Profit Per Day] > 500 THEN 'Highly Profitable'

   ELSEIF [Profit Per Day] <=0 THEN 'Unprofitable'

   ELSE 'Profitable' END

    

3. Drag CNTD (Order Date) to rows.

4. Drag YEAR (Order Date) to columns. Drill down to the month level and remove quarter from the worksheet’s view.

5. Drag the Daily Profit Key Performance Indicator field to the rows.

6. Drag the Daily Profit Key Performance Indicator field to the color on the marks card.

7. Set the mark type in the marks card to Area.

Screenshot 3.1: Dashboard daily key performance indicator profit

Implementing an LOD calculation instead of creating a bin also makes future analysis easier because the max value will be automatically updated rather than manually editing the bins.

Using aggregate as a non-aggregate

If you've worked with calculated fields before, it's highly likely you have run into the problem that you cannot mix aggregate and non-aggregate functions.

Screenshot 4.1: Common aggregate and non-aggregate problems

If this happens, you can use an LOD calculation to trick an aggregate function into behaving like a non-aggregate function. However, be cautious with this, as it can be dangerous to aggregate what is already considered an aggregated value. Always validate that what you're doing provides the correct results.

If you want to showcase the first 7 days of your sales, you will need to know what your first 7 order dates are. I tried to use the calculation: DATEDIFF (‘day’, MIN ([Order Date]), [Order Date]) =<7. The calculation does not work because it cannot mix aggregate and non-aggregate arguments.

Screenshot 4.2: Calculation contains errors

To solve this you need to take these steps.

1. Change the calculated field:
   
   

   //First 7 Days

   DATEDIFF (‘day’, {FIXED: MIN ([Order Date])}, [Order Date]) = <7

2. Drag the formula to the filter.
3. Click True.
4. Drag Order Date to rows. Right Click and change it to Day and make it discrete.
5. Drag Sales to rows and make it discrete.

Personally, I cannot wait for LOD calculations to become more popular and to learn more about them. In addition, I want to give a little shout out to the Tableau community, Tableau’s blog, and the Flerlage Twins where I learned about LOD calculations and their use cases. Thank you for reading my blog post.

- Saba