How do LOD Expressions work in Tableau?
The aim of any BI tool is to give Data Analysis a better flow. If one, as a professional, faces the difficulty of using the tool while solving a problem, the state of flow is broken. One common cause of this problem is the need to work with data that has been aggregated to different Level Of Detail in Tableau (LOD).
Tableau placing on top of the pile for the 6th time in a Row, on Gartner’s Magic Quadrant, definitely says something about its demand in the market. This is probably the best time for the Tableau certification course.
This blog will help you understand LOD expressions and discuss the following topics:
- Why do you need Level of Detail in Tableau?
- What is Level of Detail in Tableau?
- Row Level & View Level Expressions
- Types of LOD Expressions
- Aggregation and LOD Expressions
- Filters and LOD Expressions
- Creating LOD Expressions
- Data Sources supporting Level Of Detail in Tableau
- Table Calculations vs Level Of Detail in Tableau
- Limitations of Level Of Detail in Tableau
Tableau LOD: Why do you need LOD?
There often are questions that one is bound to come across when analysing data. These questions are often simple to ask, but hard to answer. They often sound something like:
To address these types of questions, a new syntax was introduced called Level of Detail in Tableau 9.0. This new syntax both simplified and extended Tableau’s calculation language by making it possible to address these questions directly.
Tableau LOD: What is LOD?
LOD Expressions represent an elegant and powerful way to answer questions involving multiple levels of granularity in a single visualization.
Level of Detail in Tableau or LOD expressions allow you to compute values at the data source level and the visualization level. However, LOD expressions give you even more control on the level of granularity you want to compute. They can be performed at a more granular level (INCLUDE Calculation), a less granular level (EXCLUDE Calculation), or an entirely independent level (FIXED Calculation).
Tableau LOD: Row Level & View Level Expressions
Row Level
In Tableau, expressions referencing unaggregated data source columns are computed for each row in the underlying table. In this case, the dimensionality of the expression is row level. An example of a row-level expression is:
[Sales] / [Profit]
This calculation will be evaluated in each row of the database. The Sales value in each row will be divided by the Profit value in that row, producing a new column with the result of the multiplication (a profit ratio).
If you create a calculation with this definition, save it with the name [ProfitRatio], and then drag it from the Data pane to a shelf, Tableau shall typically aggregate the calculated field for the view:
SUM[ProfitRatio]
View Level
By contrast, expressions referencing aggregated data source columns are computed at the dimensionality defined by the dimensions in the view. In this case, the dimensionality of the expression is view level. An example of a view-level expression is:
SUM(Sales) / SUM(Profit)
If you drag this calculation to a shelf (or type it directly on a shelf as an ad-hoc calculation), Tableau encloses it in an AGG function:
AGG(SUM(Sales) / SUM(Profit))
This is what is known as an Aggregate Calculation.
Tableau LOD: Aggregation and LOD Expressions
LOD Expression is Coarser Than View Level of Detail
An expression has a coarser level of detail than the view when it references a subset of the dimensions in the view.
For example, for a view that contained the dimensions [Category] and [Segment], you could create a level of detail in Tableau that uses only one of these dimensions:
{FIXED [Segment] : SUM([Sales])}
In this case, the expression has a coarser level of detail than the view. It bases its values on one dimension ([Segment]), whereas the view is basing its view on two dimensions ([Segment] and [Category]).
The result is that using the level of detail expression in the view causes certain values to be replicated — that is, to appear multiple times.
LOD Expression is Finer Than View Level of Detail
An expression has a finer level of detail than the view when it references a superset of the dimensions in the view.
When you use such an expression in the view, Tableau will aggregate results up to the view level. For example, the following level of detail in Tableau references two dimensions:
{FIXED [Segment], [Category] : SUM([Sales])}
When this expression is used in a view that has only [Segment] as its level of detail, the values must be aggregated. Here’s what you would see if you dragged that expression to a shelf:
AVG([{FIXED [Segment]], [Category]] : SUM([Sales]])}])
An aggregation(in this case, average) is automatically assigned by Tableau. You can change the aggregation as needed.
Adding an LOD Expression to the View
Whether a Level Of Detail in Tableau expression is aggregated or replicated in the view is determined by the expression type and granularity.
- INCLUDE expressions will have either the same level of detail as the view or a finer level of detail than the view. Therefore, values will never be replicated.
- FIXED expressions can have a finer level of detail than the view, a coarser level of detail, or the same level of detail. The need to aggregate the results of a FIXED level of detail depends on what dimensions are in the view.
- EXCLUDE expressions always cause replicated values to appear in the view. When calculations including EXCLUDE level of detail expressions are placed on a shelf, Tableau defaults to the ATTR aggregation as opposed to SUM or AVG, to indicate that the expression is not actually being aggregated and that changing the aggregation will have no effect on the view.
I hope this blog was informative and added value to your knowledge.
If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, Python, Ethical Hacking, then you can refer to Edureka’s official site.
Do look out for other articles in this series which will explain the various other aspects of Tableau.
7.Step By Step Guide To Learn Tableau Public
8. Tableau Desktop vs Tableau Public vs Tableau Reader
9. How to Create and Use Parameters in Tableau?
10. What are Sets in Tableau And How To Create Them
11. Data-blending
13.Top 50 Tableau Interview Questions You Must Prepare In 2020
Originally published at www.edureka.co on September 20, 2021.
