Level of Detail or LOD in Tableau

To learn Tableau in depth, watch this Video by Intellipaat:

LOD Expressions in Tableau

Row Level Expressions

The dimensionality of the expressions referencing the unaggregated data columns which are computed for each row in the underlying table is known as row-level expressions.

For Example: [sales]/[order count]

This calculation will be performed in each row of the database. In each row, the sale value is divided by the order count producing a new column containing the result.

View Level Expressions

Unlike Row-level expressions, View-level expressions are the expressions that refer to the aggregated data columns and are computed at the dimensionality defined by the dimensions in the view.

LOD Expressions Syntax

The general syntax for the LOD Expressions in Tableau follows the Syntax as shown below.

The syntax for INCLUDE LOD Expressions in Tableau:

{[ INCLUDE ] < declaration of the dimension > : <expression to aggregate>}

The syntax for EXCLUDE LOD Expressions in Tableau:

{[ EXCLUDE ] < declaration of the dimension > : <expression to aggregate>}

The syntax for FIXED LOD Expressions in Tableau:

{[ FIXED ] < declaration of the dimension > : <expression to aggregate>}

To understand the LOD Expressions in Tableau in a much better and interactive way, it’s time to dive into the next segment. You will practically understand the various types of LOD expressions and execute them practically.

Types of LOD expressions in Tableau

There are three types of LOD expressions in Tableau. They are-

1. FIXED LOD: It computes values using the mentioned dimensions without any other dimensions in the view.
2. INCLUDE LOD: It computes values using the mentioned dimensions along with whatever dimensions are present in the view.
3. EXCLUDE LOD: It subtracts the mentioned dimensions from the view level of detail.

How to Create a FIXED LOD

The following procedures will help us create the FIXED LOD Expressions in Tableau.

• Please create a new sheet and rename it as “FIXED Sheet” for reference
• Go to the Analysis option and create a new calculated field

• Rename the calculated field as “Sales by Region”
• Write down the following formula in the calculated field section

{ FIXED [Region] : SUM([Sales])}

• Now drag Region and States to the columns.
• Let us drag the “Sales by Region” calculation field to rows
• The resultant visualization will look something like this below

With this, you will move to the next part, where you will learn the fundamental differences between the Table and LOD Expressions in Tableau.

How to Create INCLUDE LOD Expressions

• Please create a new sheet and rename it “INCLUDE Sheet” for reference
• To implement the LOD Expressions, you need to create a visualization
• Drag region to columns, and sales to rows
• Tableau will auto generate a bar chart as shown below

• Now the next step is to create a calculated field
• Select the analysis option, choose the “Create Calculated Field” option

• Now our next step is to add the formula to the calculated field

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

• Now drag the newly created calculated field from the measures panel to rows
• Tableau will automatically create two bar charts, as shown below

• Now, the last step is to change the aggregation to average
• To do so, right-click on the “sales per customer” pill
• Select the “Measure” option
• Click on the average option in the drop-down

• The resultant visualization will be as shown below.

With that, you will now head to the next type of LOD Expressions in Tableau, which is the EXCLUDE LOD Expressions in Tableau.

How to Create EXCLUDE LOD Expressions

The below-mentioned steps are followed to generate the EXCLUDE LOD Expressions.

• Please create a new sheet and rename it as “EXCLUDE sheet” for reference
• Create a new calculated field
• Go to Analysis
• Select the option of “Create Calculated Field”

• Rename the Calculated field as EXCLUDE Calculation
• Write the following formula in the calculation field

{ EXCLUDE [Region] : SUM([Sales])}

• Now you must drag the region and sales to the rows
• And order date to columns (aggregate the date to month-wise)

• Tableau will automatically create a line graph
• To present it in a readable way, change it to the bar graph
• Go to the marks card and change the option from automatic to bar chart

• The updated visualization looks like this

• Now drag the EXCLUDE calculation to colors on the marks card

• The previous step will help you improve the readability of the visualization
• The final visualization is as follows

FIXED Level of Detail Expressions:

To find the number of sales for each state in each ship mode, we can create a calculation field by clicking on the “analysis”. The following is the FIXED expression for this example.

After dragging the ship mode and state to the columns field, it would show the following table with the fixed ship mode to the states.

INCLUDE Level of Detail Expressions:

To calculate the sum of sales per category for each sub-category of products, drag the sub-category to the “Rows” shelf and write the INCLUDE expression in the columns shelf. The following graph will be shown on the screen.

EXCLUDE Level Of Detail Expressions:

In this example, we are excluding ship mode by using EXCLUDE expressions and plotting the chart to visualize the scenario.

Why use LOD in Tableau?

There are a number of questions that arise during analyzing and visualizing data. For example, if it is possible for us to plot such a chart where we can find the highest sales each sub-category item has received, then show the averages of each category? It becomes complex, right! To address such questions, a new syntax called Level of Detail was introduced.

Top LOD expressions in Tableau

The following are the top applications of LOD expressions in Tableau.

1. Customer Order Frequency
2. Cohort Analysis
3. Daily Profit KPI
4. Percent of Total
5. New Customer Acquisition
6. Comparative Sales Analysis
7. Average of Top deals by sales rep
8. Actual vs. Target
9. Value on the Last Day of a period
10. Return purchase by cohort
11. Percentage difference from average across a range
12. Relative period filtering
13. User Login frequency
14. Proportional Brushing
15. Annual Purchase frequency by customer cohort

Customer Order Frequency:

Find the number of customers and the number of orders they made. It can be solved as follows:

• {FIXED [Customer Name]: COUNTD([Order Id])}.
  • Create a new calculation field and type the above expression and name it as customer orders.
  • Drag and drop this newly created field and customer name in columns and rows shelf respectively.
  • Then click on customer name and sort by field “customer name” with aggregation as count(distinct).
  • Change the measure to count(distinct), select dimension, and discrete.

Cohort Analysis in Tableau:

For any business, retaining old customers is as important as getting new customers. Cohort Analysis is one such analysis that helps us in analyzing the behavior of existing customers and allows the company to bring forward the recommendations according to their taste. Cohort analysis takes the given data as input and breaks them into several cohorts for analysis. Usually, these users have similar attributes or behavior.

• {FIXED : [customer name]:MIN([order date])}.
  • Create a new calculation field and name it as Customer purchase date and write the above-mentioned expression.
  • After that drag sales and order date to rows and column shelves respectively.
  • Add customer purchase date to the color field.

Daily Profit KPI

The Daily Profit KPI analysis helps in easily creating bins based on aggregate data like per day profits, where the underlying data is recorded at a transactional level. This type of analysis is typically used when we want to know in detail about the number of profitable days of a month or even a year. So, if we wish to measure our success by the total profit per business day, we can use the Daily profit KPI analysis. The following ways show how the LOD expressions can be used:

• {FIXED [Order Date]: SUM ( [ Profit ])} – Using this, the sum of the profits can be FIXED to each day of sales.
• Also, we can simply write a logical statement using the syntax- If [Profit per day] > 2000, then “Highly profitable”, ELSEIF [Profit per day] <=0, THEN “Unprofitable”, ELSE “Profitable” END

Following image shows a sample Daily Profit KPI analysis

Percent of Total

Using this LOD expression, you can filter a market along with reducing recalculation, etc. The Percent of Total method helps you to filter on a market and still measure the global contribution. You can easily compute one market’s/ country’s percent to total global sales. The syntax goes as follows:

Giving the syntax- SUM (Sales), returns sales for each country. However, the syntax- { SUM (Sales)}, returns total sales globally. A sample image is shown below:

New Customer Acquisition

This LOD expression lets you easily identify the market-wise daily trend in customer acquisitions. The New Customer Acquisition method helps you understand how well your marketing and sales organizations are performing across regions.

A steeper line indicates that the trend of customer acquisition is better. However, if you arrive at a flattened line, you must take prompt actions as something might be wrong. A sample image for this LOD expression is shown in the below image.

Table calculations vs. Line of Detail Calculations

Tableau Prep LOD

Tableau Prep is a personal data preparation tool that helps users to cleanse, aggregate, merge and prepare their data for analysis. It has a simple and clean user interface. The various transformation steps within Tableau Prep are very helpful. Let’s see how we can make LOD expressions work in Tableau prep.

LOD expressions in Tableau Prep

The ability to use LOD in Tableau Prep is one of the much-awaited innovations in the tool. From version 2019.3 onwards these features were available for everyone. There is no visualization in Tableau Prep. It only displays a table with data rows and columns. By using LOD calculations in the Tableau prep the data can be easily combined in the same table at different aggregation levels.

Nested LOD Tableau

Nested Level Of Details is a very confusing topic and is mostly needed only when one needs to aggregate an already aggregated value to a higher LOD. For example, if we consider the sample data source in the Tableau i.e Superstore, and we want to find the average sales per order by region and ship mode across all the regions, this calls for the use of nested LOD.

Constraints of LOD

The following are the limitations of LOD expressions:

• LOD expressions which refer to the floating-point measures behave in a bizarre fashion when they are used in a view that needs a comparison of the values of expressions.
• 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 a parameter name while referencing a parameter in dimensionality declaration. The parameter value cannot be used in such cases.
• In the case of data blending, before using a Level Of Detail expression from a secondary data source, the linking field from the primary data source should be in the view beforehand.

Level Of Detail in Tableau, thus, made the solutions to complex queries easy to handle. LOD expression is a powerful way to answer questions that have multiple levels of granularity in a single visualization.