20 Calculation Use Cases: LOD vs Non-LOD

[This is #TweakThursday series No.44, a series of viz critiques.]

Tableau Zen Master Ken Flerlage published 20 use cases of Level of Details or LOD a few days ago. I am quite curious as to how different it is to implement them without LOD. So I recreated 19 of 20 examples using simple aggregations or table calculations. Here is the workbook that contains both Ken's original LOD examples and my non-LOD calculations. Hover on the charts to view and compare the formula in LOD and non-LOD.

The main takeaways from these exercises are:
1.In general, LOD and non-LOD are both possible options to implement almost any calculation. One can use either depending on one's mastering of either technique.

2.One can be better than the other depending on use cases.

3.Table calculations can be conceptually more challenging than LOD. LOD is conceptually simpler to understand. Still it depends on one's understanding of either technique.

4.Both options involve similar complexity. Only in some extreme cases with AGG() type of non-linear aggregations, LOD shows greater advantages over non-LOD.

5.When referring to measures derived from dimensions out of view, LOD is more advantageous.

Let us start with all the 20 cases as follows. We mostly explain how to implement the use cases without LOD. In the process, we will compare with LOD implementations and comment on the advantages of one or the other.

1. Deal with Duplicate Records
The easy way to handle this is Avg(Sales).

2. Get a Single Aggregate
The non-LOD solution is TOTAL(MAX([Sales])) computed along Row ID, given one record per row. Otherwise, we should use Window_Max(SUM(Sales)) .

3. Isolate a Specific Value
Use the basic table calculations to compute along Category and Order Date

• WINDOW_MAX(MAX(IF [Category]='Technology' THEN [Order Date] END))

4. Synchronize Chart Axes
The LOD solution is fairly simple:

Region Month Sales:

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

Max Regional Monthly Sales:

• {FIXED : MAX([Region Month Sales])}

Use this as vertical reference line on each of the 4 regional charts to synchronize them in height.

For the non-LOD solution, create first the sales in each of the 4 regions
Sales West: If Region='West' Then Sales End
Max Sales West: WINDOW_MAX(SUM([Sales West])) computing over Order Date.
Same for the other 3 regions.

Find the Max(Sales) among regions ad follows

• MAX(
•       MAX([Max Sales East], [Max Sales West]),
•       MAX([Max Sales South], [Max Sales Central])
• )

Use the Max over region as vertical reference line to synchronize the vertical data range.

5. Find Min/Max in a Time Series
Use this formula to find sales at min and max:

• IF  SUM([Sales]) =WINDOW_MIN(SUM([Sales])) OR
•       SUM([Sales]) = WINDOW_MAX(SUM([Sales]))
• THEN
•     SUM([Sales])
• END

and compute over Order Date.

It is a bit simpler than the LOD solution.

6. Get Related Data
To make the total sum of sales by a particular customer available everywhere, just use the following table calculation:

• TOTAL(SUM(
•     IF [Customer Name]=[Selected Customer] 
•     THEN [Sales]
•     END))

Computation wise, it's similar to LOD formula. The main difference is the previous one is aggregated and the latter is non-aggregated which will be aggregated in the table anyway.

• {FIXED : SUM(
•     IF [Customer Name]=[Selected Customer] 
•     THEN [Sales]
•     END)} 

7. Turn Row Values into Measures
The non-LOD formula is simple:

• IF [Measure]="Sales" THEN [Value]  END

The LOD formula is

• {FIXED [Region]: SUM(IF [Measure]="Sales" THEN [Value] END)}

The complexity is similar between LOD and Non-LOD. Both results are non-aggregated. Besides, Profit can be calculated similar to Sales without LOD. Profit ratio can be computed by Sum(Profit)/Sum(Sales) without LOD.
8. Get Point-in-Time Data

//Customer Last Order Sales LOD

• IF [Order Date]={FIXED [Customer Name]: MAX([Order Date])}
  
• THEN [Sales] END

//Customer Last Order Sales (For Comparison) LOD

• {FIXED [Customer Name]: SUM(Customer Last Order Sales )}

//Customer Last Order Sales (No LOD).//Last Date is a set for the Max Order Date.

• IF [Last Date] THEN [Sales] END

//Customer Last Order Sales No LOD (Compare)
//Compute along Order ID

• TOTAL(SUM([Customer Last Order Sales No LOD]))

9. Calculate the Mode
//Quantity Appearances

• {FIXED [Quantity]: SUM([Number of Records])}

//Max Appearances

• {FIXED : MAX(Quantity Appearances)}

//Quantity Appearances No LOD (computing along Row ID)

• TOTAL(COUNTD([Row ID]))

//Max Appearances No LOD (computing along Row ID and Quantity)

• WINDOW_MAX([Quantity Appearances No LOD])

10. Get Value from a Set
//Date from Set

• {FIXED : MAX(IIF([Date Set],[Order Date],NULL))}

//Date from Set No LOD

• MAX(IIF([Date Set],[Order Date],NULL))

11. Use Aggregate as a Non-Aggregate
There are three use cases in this example. LOD presents a very simple solution to the calculation of Grand Total (the 2nd use case). There is no good solution to the problem without LOD.

12. Compare Current & Previous Year
//Difference 2018 to 2019 LOD

• {FIXED MONTH([Order Date]): SUM(IIF(YEAR([Order Date])=2019, [Sales], NULL))}
• -
• {FIXED MONTH([Order Date]): SUM(IIF(YEAR([Order Date])=2018, [Sales], NULL))}

//Difference 2018 to 2019 No LOD

• SUM(IIF(YEAR([Order Date])=2019, [Sales], NULL))
• -
• SUM(IIF(YEAR([Order Date])=2018, [Sales], NULL))

13. Find Occurrences of a Measure
//Orders Above Threshold -Use Dual Axis to label the bar.

• IF {FIXED [Order ID]: SUM([Sales])} > [Order Threshold]
• THEN 1
• ELSE 0
• END

//Use reference line instead of text label.
//Created a set for those higher than threshold. Used the set as a filter for the bar.
//Calculate without LOD

• COUNTD([Order ID])

14. Compare a Ratio to the Max
//Month Sales % of Max LOD

• SUM([Sales])/ATTR({FIXED [Sub-Category]:
• MAX({FIXED [Sub-Category], DATETRUNC('month', [Order Date]): SUM([Sales])})})

//Month Sales % of Max No LOD computing along Order Date

• SUM([Sales])/WINDOW_MAX(SUM([Sales]))

15. Compare Subset to Superset
//National Average Sales LOD

• {FIXED [Country/Region]: AVG([Sales])}

//Avg Sales No LOD

• TOTAL(AVG([Sales]))

16. Get First Occurrence of an Event
//First Purchase Over 10 LOD

• {FIXED [Product Name]: MIN(
• IF [Quantity]>10 THEN [Order Date] END
• )}

//First Date over 10 No LOD

• TOTAL(MIN(IF [Quantity>10] THEN [Order Date] END))

17. Find Record Meeting Some Criteria
// Create Bought One Category and count customers in the category=1
// Dual axis to apply 2 labels
// LOD

• IF {FIXED [Customer Name]: COUNTD([Category])}=1
• THEN 1
• ELSE 0
• END

//Create a set Bought 1 Category and use it as a filter
//Use one label and one reference line for labeling
//No LOD

• CNTD(Customer Name)

18. Count Items Selected in a Filter
//Filter Selection Count (LOD)

• {FIXED : COUNTD([Sub-Category])}

//Filter Selection (No LOD) computing over Sub-Category

• TOTAL(COUNTD([Sub-Category]))

19. Rank Numbers (Kind of)
// Rank the sales by Segment (LOD)
// Sales matches the maximum sales for a segment

• IF SUM([Sales]) = ATTR({FIXED : MAX({FIXED [Segment]: SUM([Sales])})}) 
• THEN 1

// Sales matches the minimum sales for a segment

• ELSEIF SUM([Sales]) = ATTR({FIXED : MIN({FIXED [Segment]: SUM([Sales])})}) 
• THEN 3
• ELSE 2
• END

//No LOD
//Use Quick Table Calculations for Rank

• Sum(Sales)

20. Using Filters, Show an “Other” Value
//Sales Adjusted LOD

• IF ATTR([Sub-Category New]) = "Other" THEN
• MAX([Total Amount]) - SUM([Sales])
• ELSE SUM([Sales])
• END

//Total Amount LOD

• {FIXED : SUM([Sales])}/2

//Sales Adjusted No LOD:

• IF ATTR([Sub-Category New]) = "Other" 
• THEN SUM([Sales])
• - ZN(TOTAL(SUM(IF [Sub-Category New]!= "Other" THEN [Sales] END)))
• ELSE SUM([Sales])
• END