During my Corporate Tableau Training in Gurgaon , i get questions many time regarding LOD in Tableau . LOD – Level of Detail is the most important concept introduced in Tableau 9 and above . In this section we try to explain Level of Detail and then in ther other sections we explain LOD through different use case .
For Corporate training and Online Training contact at info@instrovate.com
Level of Detail
LOD Expressions allow you to determine the levels of detail (i.e., the dimensions) used in a calculation without actually dropping those dimensions into the visualization. You can define the level of detail at which a calculation should be performed, independently of the Viz LOD.
Unlike table calculations, totals, or reference lines, level of detail expressions are computed in the data source. On the plus side, this lets you avoid the overhead of bringing all the data from the database to your computer over the network. With large data sources, this can be a huge performance gain. On the minus side, this can cause Tableau to run more complicated queries (for example, containing many joins), and if the underlying data source is slow, performance can suffer.
Overview: Level of Detail Expressions
Level of detail expressions (which are sometimes also referred to as “LOD Expressions” or
“LOD Calculations”) are useful for a variety of use cases, including:
-
Cohort analysis – comparing data for different subgroups
-
Totals or averages across segments.
-
Aggregates of aggregates
-
Binning aggregates
Dimension and set fields placed on any of the locations highlighted in the following image contribute to the view level of detail:
LOD Expressions allow you to determine the levels of detail (i.e., the dimensions) used in a calculation without actually dropping those dimensions into the visualization. You can define the level of detail at which a calculation should be performed, independently of the Viz LOD.
LOD is created in calculated field and it gives a new field, in the following manner:
LOD Expressions are enclosed in curly braces. The first piece inside the curly brace is a keyword, either FIXED, INCLUDE, or EXCLUDE. After the keyword, we enter dimensions that the keyword will act on. FIXED computes the value using the specified dimension without reference to any other dimensions in the view. INCLUDE computes the value using the specified dimension in addition to whatever other dimensions are in the view. In our example here, we included the Order ID, in the LOD expression, along with the dimension in the view (in our case, Country). The last keyword option is EXCLUDE, which will ignore the specified dimension even if it is used in the view. Next, enter a colon. This tells Tableau that we’re done declaring the Level of Detail and are about to enter the aggregate expression. Finally, the aggregate expression itself – this is what we actually want the calculation to do, such as give us a MIN([Order Date]) or AVG([Discount]).
RESULT OF THE LOD EXPRESION:
Level of Detail Expressions that use EXCLUDE or INCLUDE keywords will always result in measures. FIXED keyword expressions will default to be dimensions or measures based on the aggregate expression. If the aggregate expression would yield a measure, the overall expression will be a measure, such as Number of Orders per Customer. If the aggregate expression would yield a dimension (such as string, Boolean, or date), the overall expression will be a dimension, such as Second Purchase.
KEYWORDS OF LOD:
There are three types of LOD expression keywords—EXCLUDE, INCLUDE and FIXED—each of which alters the scope of the LOD expression.
Fixed: Specifying The Exact Level Of Detail
FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view. FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view. FIXED level of detail expressions also ignores all the filters in the view other than context filters, data source filters, and extract filters.
Example: {FIXED [Region] : SUM([Sales])}
This level of detail expression, named [Sales by Region], is then placed on Text to show total
sales per region:
The view level of detail is [Region] plus [State], but because FIXED level of detail expressions do not consider the view level of detail, the calculation only uses the [Region] dimension, and so the values for the individual states in each region are identical.
Include: Calculating At A Lower Level Of Detail
INCLUDE level of detail expressions compute values using the specified dimensions in addition
to whatever dimensions are in the view.
INCLUDE level of detail expressions can be useful when you want to calculate at a fine level of
detail in the database and then re-aggregate and show at a coarser level of detail in your view.
Fields based on INCLUDE level of detail expressions will change as you add or remove
dimensions from the view.
So, in the above eg. if the INCLUDE keyword had been used in the level of detail expression instead of FIXED, the values would be different for each state, because Tableau would add the dimension in the
expression ([Region]) with any additional dimensions in the view ([State]) in determining values for the expression. The result would be:
Exclude: Calculating At A Higher Level Of Detail
EXCLUDE level of detail expressions declare dimensions to omit from the view level of detail.
EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.
EXCLUDE level of detail expression cannot be used in row-level expressions (where there are no dimensions to omit), but can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression).