Level of Detail Expressions

In the previous chapter, we covered table calculations, which are extremely useful when we want to aggregate information using exactly the data that is in the view, but sometimes we want to use more (or less) than the information that can be seen on the screen. For instance, if you want to show the average spend by customer, you don't want to show all customers and their value to compute an average. This is where Level of Detail (LOD) calculations, which we will introduce in this chapter, can be used, because they help us change the level of aggregation.

The following topics will be covered in this chapter:

• Tableau's order of operations
• FIXED LOD calculations
• INCLUDE LOD calculations
• EXCLUDE LOD calculations
• Data source constraints for LOD

Technical requirements

This chapter uses the Global Superstore dataset, which can be found at http://www.tableau.com/sites/default/files/training/global_superstore.zip.

Tableau's order of operations

Before delving deeper into LOD calculations, it is first important to understand Tableau's order of operations, as this will guide our conversation, seeing where this new topic fits in.

Surprising results

Especially when coming to Tableau from Excel or other BI tools, one is sometimes at a loss when dealing with Tableau's order of operations. What can seem intuitive is not always what is reflected. Applying the wrong type of filter, or not using the proper calculation, will result in meaningless results.

The following is one such scenario. In the Global Superstore dataset, we create a data source filter for Country to show United Kingdom orders only, a dimension filter for State...

FIXED LOD calculations

FIXED is the first of three LOD calculations we will study in this chapter. It is used to make a calculation regardless of any dimension or measure filters that are applied in the workbook. The syntax of FIXED is the following: {FIXED [dim1[, dim2]…] : aggregate-expression}, with [dim1[, dim2]…] a list of dimensions along which the aggregation is to be calculated.

Please note the use of curly brackets. The syntax will be the same for all three LOD expressions we describe in this chapter.

For instance, let's look at the date of first order for a given customer. While a MIN([Date]) calculation will give the first date in the filtered dataset with Customer Name used as a row or column, {FIXED [Customer ID] :MIN([Date])} will give the first date for a customer in the entire dataset, filters notwithstanding, for each Customer ID. As a result...

INCLUDE LOD calculations

The INCLUDE LOD calculation is used to add a layer of aggregation that will not be used as part of the dimensions. For instance, you might want to see the average customer's lifetime value in different markets. Under normal circumstances, you would need to first aggregate at customer level, and then use the results of that aggregation to compute an average. In the SQL world, we would use a subquery. Simply averaging all sales will not yield the same results, as it will be computed at the line item level and will therefore give the average value of a line item.

We can understand this by saying that we want to aggregate at a level that includes both the dimensions already in the view and another set of additional dimensions. This means that we have data that is more granular than what will be shown in the view, and therefore that we will need to further...

EXCLUDE LOD calculations

While INCLUDE LOD calculations enabled us to calculate along the view dimensions and the further dimensions specified, EXCLUDE LOD calculations enable users to make calculations omitting one of the dimensions that have been used in the view. For instance, we can use EXCLUDE to calculate the total sum of profits for one category across regions, even if Region is used as a row. This is especially useful when dealing with contributions to a total.

Example 1 – contribution to total

Using the Global Superstore dataset, we want to show contributions to total profit for the South region of the EU market, slicing by country, category, and subcategory. What is the contribution of the Fasteners subcategory...

Data source constraints for LOD

While LOD calculations are in general supported in Tableau, there are a few constraints on certain data source types. At the time of writing, here are the most notable exceptions to the rule:

Data source	Support
Google Big Query	Supported for standard SQL, not supported for legacy SQL
Microsoft Access	Not supported
Microsoft Jet-based connections (legacy connectors for Microsoft Excel, Microsoft Access, and text)	Not supported
Microsoft SQL Server	SQL Server 2005 and later
Mongo DB	Not supported
Oracle	Supported version 9i and later
PostgreSQL	Supported version 7 and later

A complete and up-to-date version of this list can be found at https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_constraints.htm.

Summary

In this chapter, we covered the three types of LOD expressions available in Tableau (FIXED, INCLUDE, and EXCLUDE) and studied how we can use them to aggregate data at a level that is either more granular (in the case of INCLUDE) or less granular than the dimensions already in the view. We also looked at the order of operations to explain the differences between these calculations and the ones seen previously (such as table calculations). For instance, we now hold the tools to calculate contributions to a total, create cohorts based on first order date, and aggregate customer-level information starting from item-level data.

Here is a quick reminder:

	FIXED	INCLUDE	EXCLUDE
Order of operations	Before dimension filters	After dimension filters	After dimension filters
Purpose	Calculate across the dataset along selected dimensions	Calculate results using a dimension that...

Questions

Answer the following questions to test your knowledge of the information in this chapter.

Q: If I'm not using any dimension filters, is there a difference between using a Percent of Total table calculation and using FIXED/EXCLUDE calculations?

A: While they will, in general, yield the same results, there a few exceptions that we have discussed in this chapter, and the main reason for the differences is the order of operations. FIXED calculations will remain unchanged by dimension and measure filters, EXCLUDE calculations will remain unchanged by measure filters (but not by dimension filters), and table calculations will be affected by both dimension and measure filters.

Q: Do LOD calculations have to use fields that are already included in the view? Can LOD calculations aggregate at a different level than the one determined by the fields chosen in the view?

A:...

Further reading

You can check out the following links for more information about the topics that were covered in this chapter:

• Top 15 Tableau LOD Expressions: https://www.tableau.com/about/blog/LOD-expressions
• Data Source Constraints for Level of Detail Expressions: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_constraints.htm
• Which calculation is right for your analysis: simple, table, or LOD? https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_understand_which.htm