In this blog post I will examine what LOD Expressions are, when and how to use them in Tableau. It took me a while to understand how they work and when to use them. The root of the confusion for me was that they happen behind the scenes. This post is for you if you just stumbled across these tricky calculations.
What is LOD?
In Tableau, the results for a calculation such as SUM([Sales]) depend on the context. The context is defined by the filters and level of detail. Dimensions on row or column shelves or color, size, label, detail or path marks card define the level of detail for the sheet.
Every time you place a dimension on a column, row or mark card, you change the level of detail. By placing pills on your shelves or marks, you increase the granularity which means deeper level of detail. The view will change according to the dimensions you place on the shelves or marks.
LOD Expressions
LOD Expressions allow you to determine the dimension used in a calculation without dropping those dimensions into the visualization. They allow you to answer questions requiring different level of details in a single visualization. You can aggregate in the viz background without changing the viz itself. Before these expressions were supported in Tableau, it was not possible to create calculations at a level of detail other than the view level.
There are three types of LOD expressions: FIXED, INCLUDE, EXCLUDE.
The Syntax for LOD Expressions: {TYPE OF LOD EXPRESSION [Dimension] : AGGREGATE ([MEASURE]) }
Example:
Order of Operations
It is important to remember the order in which Tableau executes operations as it can have big impact on the result in the view. As you build a view, add filters and calculations involving LOD expressions, those filters and calculations always execute in the order of operations.
Tableau’s order of operations:
As the above diagram shows Fixed LOD expressions are executed after Context filters but before Dimension filters. However Include and Exclude LOD expressions are evaluated after Dimension filters but before Measure filters.
Data Source Constraints for LOD Expressions
You can’t use LOD Expressions with all kind of data sources. The following data sources are not supported or partially supported:
| Data Source | Support |
| Actian Vectorwise | Not supported. |
| Cubes (multidimensional data sources) | Not supported. |
| DataStax Enterprise | Not supported. |
| Generic ODBC | Limited. Depends on the specific data source. |
| 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. |
| Splunk | Not supported. |
Summary
LOD Expressions are powerful tools to improve flexibility in your analyses. Using LODs you can calculate and visualize things like AVG(Sales) by Category minus AVG(Sales) for the entire data set to see how sales per Category compare to the overall average.
This post is the first in a series about LOD Expressions. The following posts will discuss each LOD expression types in more detail.
