Tableau: Level of Detail Expressions/ LOD

This blog is all about Tableau’s “Level of Detail Expressions/LOD”. Here you will be reading about different types of LOD Expressions and how to create LODs in Tableau.

What is LOD?

Level of Detail Expressions allows users to perform computation at a granularity level or visualization level of detail. These are used to run complex queries at a data source level instead of bringing all the data to the tableau worksheet.

So the keyword here is ‘granularity’, sometimes I found this word as the biggest barrier to understanding the ‘LEVEL OF DETAIL EXPRESSIONS’, so granularity in general means the ability to manipulate/display the small, discrete pieces of data as opposed to the larger groups.

Syntax of LOD Expressions-

{ TYPE [dimension list] : [Aggregate] }

Parameters of LOD Expressions-

1-{ }: It should be made sure that the expression is always enclosed within curly braces.

2-TYPE: This specifies the type of LOD expressions to be used in the expression.

3-Dimension list: This is where you specify the dimension to be used in your calculation, multiple dimensions can be mentioned here separated by a comma.

4-Aggregate: This is where you specify the aggregate condition on any dimension.

Types of LOD Expressions-

These are three types of LOD Expressions in Tableau-

1: FIXED LOD-Whenever we are forming a LOD in Tableau it consists of one or more dimensions, sometimes it is already present in the view and sometimes it’s specified by the user. So FIXED LOD involves an expression that only considers the dimension specified by the user i.e, it’s independent of the view. Therefore, in the FIXED LOD type, the calculation only computes the data values based on the user-specified dimension.

I am using Sample Superstore Dataset here for every example

Let’s take an example to suppose that we have to calculate the total sales by region without taking the sales for each state in the region we will use the FIXED LOD to fix the sale for each region.

· Create a new sheet with the name “FIXED” sheet.

· Click on the Analysis tab on the top and select create calculated field

a dialogue box will open and rename the calculated field as ‘Sales by Region’.

Image by author

· Write down the Expression as:

{FIXED [Region] : SUM ([Sales])} as shown below

Image by author

· Now Drag Region and States to Column

· Let us drag our Calculated field to rows

· We will see the following resultant visualization

Image by author

NOTE- There is also a variation of FIXED LOD if you simply do not give the type of LOD and don’t specify the dimension and you simply give the { } and the aggregate then this is treated as a FIXED LOD also called ‘Fixed Table Scoped LOD’

Example: {SUM (Sales)}

2: INCLUDE LOD-This will include the dimension present in the view with the dimension specified by the user, Hence INCLUDE LOD works at the lower level of granularity.

Let’s see an example if we wish to include average sales per customer then what we have to do is

· Create a new sheet with the name ‘INCLUDE’ sheet

· Go to the calculated field as shown above

· Rename the calculated field as Sales per Customer

· Write down the expression as:

{INCLUDE [Customer Name]: SUM ([Sales])}

Image by author

· Now drag the Region to columns and Sales to Rows

· Let’s drag our created calculated field to the Rows you will get two bar charts

· But to get the average sales by the customer right click on Sales per Customer pill

• Select the “Measure” option

• Click on the “Average” option in the drop-down

Image by author

You will get the resultant visualization as shown below:

Image by author

3: EXCLUDE LOD-This level of detail in Tableau is used when we wish to omit the specified dimension in the view. ’EXCLUDE’ level of detail is majorly used to take the difference from the overall average or ‘percent of the total’.

Let’s take an example-

Create the following view by doing the following steps:

· Drag the Region, State, and City to the Rows

· Now drag the sales to the Label under Marks

Image by author

In the above view, the sales are computed at Region, State, and City Levels

Now create the EXCLUDE calculated field

· Go to the create a calculated field as shown above

· Rename the calculated field as Exclude City

· Type in the Expression:

{EXCLUDE [City] : Sum([Sales])}

Image by author

· Now drag the Measure name to the column

· Drag the Region, State, and City to the Rows

· Drag the Measure Name to the filters and select Sales and Exclude City there

· Also Drag the Measure Values to the Label and select Sales and Exclude City

You will get the following visualization:

Image by author

The above visualization shows that since we have mentioned LOD expressions to EXCLUDE City dimension when computing the sales, the sales are computed only at Region Level and State Level.

LIMITATION OF LEVEL OF DETAILED EXPRESSION IN TABLEAU-

• LOD is not present on the Data Source page

• There is a possibility of query errors in the case of complex calculation problems

• One always needs to use the parameter name while giving a parameter in the dimension list. The parameter value cannot be used in such cases.

Conclusion-So here we learned about different LOD Expressions in Tableau is a powerful way to answer questions that have different levels of granularity in a single visualization, this will help beginners to have an understanding of the LOD and how to use them to create different views.