How Do Level of Detail Expressions Work in Tableau
Updated on 07th Sep, 21 124 Views

Level Of Detail in tableau is an incredibly powerful tool for Data analytics. These LOD expressions provide a way to easily compute aggregations that are not at the level of details of visualizations and can integrate those values within visualizations in arbitrary ways. This blog will help you get familiarized with the LOD calculations by discussing the following aspects in detail.

  1. Introduction to Level Of Detail(LOD) in Tableau.
  2. Overview of Level Expressions.
  3. LOD expressions in tableau.
  4. Customer Order Frequency.
  5. Cohort Analysis.
  6. LOD Calculations Tableau.
  7. Tableau Prep LOD
  8. Nested LOD Tableau.
  9. Constraints Of LOD.

Are you new to the concepts of Tableau? Get started with Tableau tutorial for beginners and become a certified professional.

Introduction to Level Of Detail in Tableau.

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.

What is the meaning of LOD in Tableau?

Imagine, you own an online food delivery app that works in the lines of Swiggy. Using Tableau, you can analyze the following situations:

  • Highest rated restaurants.
  • Least-rated food items.
  • Different types of food and many more.

But, what if we want to know which restaurant has the highest rating on a particular day or a particular season. How would we work through such minute details?

The LOD expressions in Tableau are what we exactly need in such situations to resolve the problem. The LOD expressions in Tableau help in performing aggregations that are unavailable at a certain level of visualization.

Become a Business Intelligence Architect

Overview of Level Expressions

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. 

For Example:  SUM([Sales])/SUM([order count]).

LOD Expressions in Tableau

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.

Click on Tableau Applications to know about Tableau and its applications.

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.

ship mode sales

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.

list view

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.

include category
include graph

 

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. 

exclude
exclude graph

Top 15 LOD expressions in Tableau

The following are the top 15 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 who made one order, two orders, and so forth. 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.
customer order frequency

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 analysing the behaviour 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 to several cohorts for analysis. Usually, these users have similar attributes or behaviour.

  • {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.
cohort analysis

Check out this video to know about Tableau calculations

LOD Calculations in Tableau

Table calculations v/s LOD calculations.

Table CalculationsLOD calculations
Table calculations are done by query results.LOD calculations are created as a part of a query to the underlying datasource.
Table calculations give results equal or less specific than LOD.LOD can produce results independent of said LOD.
Dimensions which control operations of a table are different from the syntax of calculation.Dimensions which control operations of an LOD are present in the expression itself.
These are used for aggregated constructs/measures only.These can be used in other measures as well.
Filters on table calculations function as HIDE.Filters on LOD function as EXCLUDE.

Tableau Prep LOD

What is Tableau Prep?

Tableau Prep is a personal data preparation tool that helps the 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 is 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.

Master Data Visualization Using Tableau

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.

Check out our Business Intelligence Courses for more information about BI courses.

Course Schedule

Name Date
Power BI Training 2021-11-27 2021-11-28
(Sat-Sun) Weekend batch
View Details
Power BI Training 2021-12-04 2021-12-05
(Sat-Sun) Weekend batch
View Details
Power BI Training 2021-12-11 2021-12-12
(Sat-Sun) Weekend batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Looking for 50% Salary Hike ?

Speak to our course Advisor Now !

Associated Courses

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.