LOD Tableau(Level of Detail) Expressions 101: How to Create and Exploit Data?

After the data is cleaned, standardized, and transformed into an analysis-ready form, businesses require a platform that can help them gain insights from it. Tableau is a popular data visualization tool used globally for data analytics and business intelligence. Its flexibility and a user-friendly interface allow business analysts to effectively create and design dashboards, charts & graphs for simplifying the decision-making process.

Offering a greater degree of control over the visualization, LOD Tableau expressions are used to effectively customize your charts and graphs. For different use cases, Level of Detail or LOD expressions provides 3 categories namely FIXED, INCLUDE & EXCLUDE. 

In this article, you will learn how to efficiently use LOD Tableau expressions with the help of several business examples.  

What is Tableau?

Tableau is a widely used data visualization tool for data analytics and business intelligence. Launched in 2003, Tableau allows you to build visually stunning and informative charts, graphs, maps, dashboards, and stories for visualizing and analyzing data, to help in making business decisions.

This powerful tool can effectively handle structured, semi-structured, and unstructured data. With its easily accessible functions, you can create highly simplified graphs or charts for any set of complex data. 

Key Features of Tableau

Tableau offers some of the most eye-catching features in the Business Intelligence industry:

• User-Friendly: This platform doesn’t require you to have any prior programming knowledge. You can get started in minutes and begin creating visualizations using Tableau.
• Flexibility: Using Tableau, you can extract data from databases like pdf, excel, text documents, R, Hadoop, Python, or SAS to cloud databases like Flipkart, Google sheet, Netflix, Amazon.
• Robust: You can accurately create reports by joining and blending different datasets. Its powerful data discovery and exploration allow you to answer vital questions in seconds.
• Tableau Server: It also provides a centralized location to effectively manage & maintain all published data sources within an organization. With the published data you can enjoy features such as collaboration, models of security, automation, distribution, etc. 

What are LOD Tableau Expressions?

• Level of Detail(LOD) Expressions in Tableau enable you to compute values at both data source and visualization levels.
• Using LOD in Tableau, you can easily run complex queries involving many dimensions at the data source level rather than bringing all of the data to the Tableau interface.
• Providing you with more flexibility, LOD expressions Tableau lets you carry out calculations at different levels of data granularity and apply calculations at the row level (non-aggregate) or view level (aggregate).

Syntax of LOD Tableau Expressions

The syntax to be followed for LOD Tableau Expressions are as follows:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

Parameters

The different parameters of the Tableau LOD Expression are as follows:

• { } : It should always be made sure that the entire Tableau LOD Expression is enclosed within curly braces.
• [ FIXED | INCLUDE | EXCLUDE ]: This specifies the type of LOD that is to be used in Tableau. This depends on the analysis we are performing.
• < dimension declaration >: This is where you specify the dimensions to be used in your calculation. Multiple dimensions can be mentioned in a comma-separated manner such as [Region], [State], etc.
• <aggregate expression >: This is where you specify the aggregate condition on any dimension.

What are the different types of LOD Tableau Expressions?

Giving you more control over the level of granularity you want to compute. LOD Tableau expressions can be classified into the following 3 categories:

1. INCLUDE LOD

Tableau allows you to create a view i.e. a visualization or a viz in terms of a chart, a graph, a map, a plot, or even a text table. Using the “INCLUDE” Level of Detail expression, you can compute aggregations considering dimensions that are specified in the calculation and also take into consideration those dimensions that are present in the view. Hence, INCLUDE LOD Tableau Expression works at a lower level(detailed) of granularity by including an additional dimension along with the one specified by the user. It then performs the aggregation and presents the values at the higher level(overview) of granularity. 

To understand this LOD Tableau expression, you can go through a case of comparing the average customer sales across different product segments.

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

 Executing the above expression, the final view will display the segments only but you have included “Customer Name” as well so that average customer sales can be calculated. For computing, the sum of sales at a lower level the Customer Name is included and then the average is shown at the higher level via Segments.

2. FIXED LOD

Compared to the INCLUDE expression, FIXED expression aggregates the value only at the dimensions which are specified by the user in the calculation. Hence, the FIXED Expression doesn’t consider the dimensions already present in the view.

For instance, consider a case of calculating the sales across different regions. You can use the following FIXED LOD Tableau Expression:

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

The purpose of using FIXED is to calculate the value only at the regional level in the above calculation. Even if the user selects another field, for example, “State” in the view, Tableau will ignore these additional dimensions in the view. In the figure below, you observe that for a region the Sum of Sales value is constant no matter the State in that region.

3. EXCLUDE LOD

The EXCLUDE LOD Tableau expression is useful for omitting a dimension from your view. Opposite to the Include LOD Tableau expression that adds a new dimension, Exclude removes the lower level granularity dimension which is present in the view and directly calculates the value at a higher granularity level. This type of LOD Tableau expression is often used to calculate ‘difference from overall average’ or ‘percent of total’.

For example, you consider comparing total sales with the monthly sales of the East region. You can use the following EXCLUDE LOD Tableau Expression:

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

In the figure below, you can observe that the above LOD Tableau Expression omits the lower level of dimension i.e. Month to get the aggregate (sum) values of Sales at a higher level i.e. Region.

Table Calculations vs LOD Tableau Expressions

At the core, Tableau offers the following 3 types of calculations:

• Basic Calculation: These calculations are performed in the underlying data source because they are written as part of the query created by Tableau. They can be done at the data source’s granularity (a row-level calculation) or the visualization’s level of detail (an aggregate calculation).
• LOD Expressions:  LOD Expressions, like basic calculations, are written as part of the query created by Tableau and thus performed in the data source. Though LOD Tableau Expressions can operate at a granularity other than that of the data source or the visualization. They can be carried out at a more granular (via INCLUDE) or a less granular (via EXCLUDE) level, or at an entirely independent level (via FIXED).
• Table Calculations: The table calculation is done after the query is returned, so you can only work with the values ​​in the query result set.

It is often a challenge to choose between the Table Calculation and LOD Tableau Expressions or Basic Calculations for a given problem. To remedy this dilemma, you can consider the following flow of questions regarding the problem statement at hand:

Sometimes, it can be difficult to see if the answer to our decision-making questions is certain, and we may be able to solve the problem in some way until we introduce complications later. For instance, consider the question: For each age group, what percent of illnesses does each disease account for?

This is surely a percent-of-total problem, and a simple table calculation about the illness can be used to resolve this issue very quickly. However, with the added complexity of allowing users to filter specific illnesses such as Diabetes, you will observe the following:

The abrupt viz is because the result set no longer contains all the data you need. The newly added filter has omitted the Patient Count data for the other diseases. You can resolve this by converting the filter into a table calculation:

[Filter Disease]
LOOKUP(MIN([Disease]), 0) compute using Disease

Or you can employ FIXED LOD Tableau Expressions where the calculations are done before dimension filters. First, you can compute the total number of people in an age group:

[Total Patients per Disease]
{FIXED [Age]:SUM([Patient Count])}

Then you can work out the % total:

[Pct Total]
SUM([Patient Count])/SUM([Total Patients per Disease])

How to Create LOD Tableau Expressions?

To start creating and using LOD in Tableau, you can follow the simple steps given below: 

• Step 1: Setting up the Visualization
• Step 2: Generating the LOD Expression
• Step 3: Leveraging the LOD Expression in the Visualization

Step 1: Setting up the Visualization

• Step 1: Go to Tableau Desktop and link to the Sample-Superstore saved data source.
• Step 2: Open up a new worksheet and navigate to dimensions in the Data pane. You can now drag Region to the Columns shelf.
• Step 3: Similarly, from under the Measures tab in the Data pane, drag Sales to the Rows shelf.

Step 2: Generating the LOD Expression

Now, you can also generate the average sales per customer for each region using the LOD Tableau expression in the following steps: 

• Step 1: Navigate to Analysis > Create Calculated Field.
• Step 2: A Calculation editor window will pop up on your screen. You can name the calculation as Sales Per Customer and enter the following LOD Tableau expression:

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

• Step 3: Click on the OK button when you are done.

Step 3: Leveraging the LOD Expression in the Visualization

• Step 1: The newly created Sales per Customer LOD Tableau expression is added to the Data pane, under Measures. Drag Sales Per Customer to the Rows shelf and place it to the left of SUM(Sales).
• Step 2: Navigate to the Rows shelf and right-click on the Sales Per Customer option and select Measure (Sum) > Average. You will now be able to view both the sum of all sales and the average sales per customer for each region. 

Top LOD Tableau Expressions Examples

1. Daily Profit KPI

You can always observe the trend in profit over time, but what if you try to measure success by gross profit per business day? Especially if you are interested in seasonal impacts, you will want to know the number of profitable days achieved by month or year. The view given below demonstrates how to use LOD expressions to easily create a bin of aggregated data, such as daily profit while recording the underlying transaction-level data.

2. Cohort Analysis

Do customers who have been with you for a longer time contribute significantly more to sales? The view below categorizes customers based on the year of their first purchase to compare annual sales contributions across cohorts. The first purchase date will be determined by the minimum order date per customer. However, because the data in the view is not visible to the customer, you can use a LOD Tableau Expression for the Cohort Analysis to set the minimum order date for each customer.

3. Customer Order Frequency

You can easily determine how many orders each customer has placed, but what if you needed to know how many customers placed one order, two orders, three orders, and so on? To create this view, you can divide the number of customers by the number of orders placed. This is a simple question, but without LOD Tableau Expressions, it would be a challenging task to separate one measure from another.

For instance, you can take up a superstore’s sales database, which has several items per order. The unique number of orders by the customer indicates the number of orders placed by each customer. A simple LOD Tableau expression can transform the number of orders into a dimension that classifies the number of customers.

4. New Customer Acquisition

It is important to track daily trends in total customer acquisition by market. This will assist you to understand how well your regional marketing and sales organizations are working to create new businesses. The higher the slope of the line, the better the acquisition trend. When the line becomes flat, you need to take action to increase lead flow.

LOD Tableau expressions prevent repeaters from being mistakenly counted as new customers because the data needs to be evaluated at the customer level, even if the data is visually displayed by market or day.

5. Percent of Total

How much does each country contribute to global sales? Color-coding by contribution quickly shows that the United States is the largest contributor to global sales. However, you also need to focus on markets like the EU, which is a small contributor. Without the LOD expression, filtering by the market will recalculate the total percentage to show the contribution of each country to the market. Using a simple LOD expression you can easily filter in the market and measure the global contribution.

6. Average of Top Deals / Sales Rep

To know your sales representative’s performance you may ask what is the biggest deal that all sales reps have finalized? Next, looking at these top deals from sales reps, what is the average by country? Even though the data is shown at the country level, LOD Expressions enables us to deep dive into the sales rep level of detail. In the visualization given below, the average top transaction size by sales reps is high in blue countries and low in orange countries. You may use this knowledge to navigate from your country to your sales representative.

7. Comparative Sales Analysis

Finding the difference from the average is relatively easy, but what if you want to find the difference from the selected category? First, you need to separate the sales for the selected category. Then you need an EXCLUDE expression to repeat that value in all other categories. That way, you can easily tell the difference between sales in each category and the rest.

8. Value on the Last Day of a Period

Unlike sales or profit, metrics such as inventory numbers, employee headcount, or the daily close value of a stock are required to be considered differently. In such cases, it is recommended to display the value on the last calendar day of the month. In addition, you would anticipate that the moving from a month to a week would updated to indicate the value of the last day.

The following example has multiple ticker inventory data at a daily level. This view compares the daily closed value of the last day of the period to a narrow value. A simple LOD expression allows you to look down at the day level, even if the data is visually displayed at a higher level. 

9. Actual vs Target

Consider this view that shows the difference between the actual profit and the target profit for each state of the coffee shop chain. In the top view, you can notice the states exceeding the targets and the states which did not. Although, such aggregations can miss out on essential key points. In some states, all products sold in that state are above target. Other products are above the set goal because one product has exceeded the goal enough to offset all the other products that failed to reach the goal. You can use a LOD Expression to check the percentage of products sold within a state that is above target.

10. Relative Period Filtering

At times, year-to-date and month-to-month comparisons with the previous year are used to analyze performance. You can easily accomplish this by filtering by today, but what if the data is updated weekly? Suppose the last update was April 2nd, but the current date is April 8th.  A month-to-month comparison would compare April 2nd through April 8th of the previous year to April 2nd of the current year. This has the potential to cause significant concern where none is required! We can find the maximum date in the dataset using a simple LOD Expression.

11. Return Purchase by Cohort

It is often economical to motivate existing customers to buy as compared to acquiring new ones. To check on your existing customer base, you may ask How many customers take 1, 2, 3, or N quarters to make repeated purchases? How many people have never purchased repeatedly? What does this behavior look like when categorized by quarterly cohort? Using the FIXED expression, you can find the first and second purchase dates for each customer and then get the number of quarters taken for repeated purchases.

Limitations for Level of Detail Tableau Expressions

While using LOD Tableau Expressions, you may face the following challenges:

• Problem Statements requiring the use of Ranking, Recursion (e.g. cumulative totals), Moving calculations (e.g. rolling averages) & inter-row calculations (e.g. period vs. period calculations) can only be solved using table calculations.
• LOD is not shown on the Data Source page.
• LOD expressions that reference floating-point Measures tend to behave unreliably when used in views that require comparison of expression values.

Conclusion

In this article, you have learned in detail about the Level of Detail Expressions in Tableau. The LOD Tableau expressions provide you with more choices associated with the level of data granularity. Using the FIXED, INCLUDE & EXCLUDE LOD Tableau expressions you can easily create visualizations according to your business problem statement. You also observed a few of the popular use cases where using LOD expressions can significantly simplify your data visualization.

Tell us about your experience of using the Level of Detail Expressions in Tableau! Share your thoughts with us in the comments section below.