How Do Level of Detail Expressions Work in Tableau
Updated on 13th May, 22 323 Views

The following topics will be covered in this blog:

What is the meaning of LOD in Tableau?

Tableau LOD 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.

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

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

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.

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.

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

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.

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.

include category

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

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

We have explained the top 5 LOD expressions below:

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.

Check out this video to know about Tableau calculations

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 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 data source.
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 the 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

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.

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

Course Schedule

Name Date
Power BI Training 2022-05-21 2022-05-22
(Sat-Sun) Weekend batch
View Details
Power BI Training 2022-05-28 2022-05-29
(Sat-Sun) Weekend batch
View Details
Power BI Training 2022-06-04 2022-06-05
(Sat-Sun) Weekend batch
View Details

Leave a Reply

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

Looking for 100% 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.