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

Nested sorting used to be difficult to many people. Hierarchical nested sorting is even more difficult.

What is (regular) nested sorting and what is hierarchical nested sorting?

Regular nested sorting is regarding multiple dimensions and one measure. Say we have all of them on the row and we need to sort the rightmost dimension by the measure. That is the nested sorting as shown below. Usually it is regarding a static table. This can be easily done by clicking the sort button on the tool menu.
Hierarchical nested sorting is sorting a hierarchy of dimensions. A hierarchy may have any number of dimensions. We can expand(+) or close(-) the lower-hierarchy dimensions to the right side at will. The sorting by the measure needs to be respected at every level of the hierarchy, and after every expand/close action. The table is thus dynamic as shown below.
The new version of Tableau has made hierarchical nested sorting very easy. We only need to set up the sort of the top dimension in the hierarchy as shown in the picture below. They key is selecting sort by "Nested". Then we can expand/close the hierarchy and have the same sort at all levels.
That is it. You can download the workbook here.

I am shown a 3D bar chart (via custom shapes) by some people. It is created the talented Toan Hoang at Tableau Magic. Here I re-created the same chart using a little different approach, without extra file and joins.