Tableau ‘Level Of Detail’: Easily explained in terms of SQL!

Level Of Detail or LOD expressions were introduced in Tableau 9.0. LOD expressions help in visualizing data with multiple levels of granularity in a single visualization. Tableau does aggregation by default when we visualize data but if we want aggregation at a different granularity than the visualization then LOD expressions are very handy.

There are 3 types of LOD expressions.

1. Fixed

2. Include

3. Exclude

When I want to aggregate data I always think in terms of SQL. If you are like me, then you are at the right place. Lets begin without much further ado.

Consider a dataset which has PatientID, Gender(0 for Female,1 for male), Hour (The hour at which Heart rate was measured) and HR (Heart Rate). Each patient has multiple HR values taken at different ‘Hour’ in the data.

If you want to work along with the article, here is the github link to download the dataset. Please click download at the right hand side to download the Dataset2.csv file.

https://github.com/VandanaKnnn/Data-for-Medium

Fixed LOD:

Fixed LOD can be used to do aggregation at the same level of granularity, Coarser level of granularity or finer level of granularity as the visualization.

If we want to calculate an average HR value per patient grouping by PatientID It can be done in SQL by:

Aggregations in Tableau happen automatically depending on the dimensions added in the visualization.

If we try to calculate an Average Heart Rate value in Tableau by selecting aggregate measure Average, level of aggregation in visualization is different.

It does ‘Group by’ both ‘Patient_ID’ and ‘Hour’ dimensions. To achieve the same results as the above SQL statement where aggregation is done by only ‘Patient_ID’ we need to use the Level of Detail expressions. This does aggregation at a coarser level compared to the visualization and generates one value per Patient_ID.

Lets create a calculated field by clicking on the Analysis > Create calculated field option. Lets name it AvgHRPerPatient. The expression will be:

{Fixed [Patient_ID]:Avg(HR)}

This will aggregate all the HR values and produce one Average HR value per PatientID.

Below table shows Avg.HR as the visualization level of aggregation and AvgHRPerPatient column as the aggregation calculated using the Fixed LOD expression.

Include LOD:

Include Lod expression is used to do aggregation with a specified dimension not in the visualization. This does aggregation at a finer level in the expression and then re-aggregate at the coarser level in the visualization.

‘Hour’ is added to the rows. HR is added to the rows and changed to avg by right clicking and selecting measure. Consider creating a calculated field AvgHRINCLUDE by using the Include LOD expression:

{ INCLUDE[Gender]:avg([HR]) }

In the screenshot above it shows the Avg(AvgHRINCLUDE) for values calculated using the LOD expression and Avg(HR) is visualization level values.

In terms of SQL, the Avg(AvgHRINCLUDE) is got by following SQL statement.

This is what happens behind the scenes in Include LOD expression where ‘Gender’ dimension is not actually added in the visualization but included in the calculation by specifying explicitly. This aggregation is at a finer level. When this calculated field is added to the visualization Re-aggregation happens at a coarser level because of the ‘Hour’ dimension.

Exclude LOD:

This does opposite to Include LOD. It excludes a specified dimension from the visualization.

Consider the situation where Hour, Gender and Avg(HR) is added to rows.

Create a calculated field AvgHREXCLUDE. The expression of this calculated field will be

{ EXCLUDE[Gender]:avg(HR)}

The Avg(HR) does aggregation considering both Hour and Gender whereas the calculated field AvgHREXCLUDE does aggregation excluding the ‘Gender’ as specified by the LOD expression.

In terms of SQL it will be just

That’s why the values are same, irrespective of the 'Gender' in the AvgHREXCLUDE column.

This is all about Level of Detail Expressions. I hope you enjoyed learning and connecting with SQL as much as I did.

References:

1. https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_exclude.htm#:~:text=The%20INCLUDE%20keyword%20takes%20the,context%20can%20offer%20different%20results.

2. https://help.tableau.com/current/pro/desktop/enus/calculations_calculatedfields_lod.htm

3. https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_include.htm

4. https://absentdata.com/level-of-detail-calculations/

5. https://absentdata.com/tableau/include-level-of-details-lods-expressions-in-tableau/