Aug
8
2016

Level of Detail Expressions in Tableau

This article explains the use of LoD expressions in Tableau.LOD Expression represent an elegant and powerful way to answer questions involving multiple levels of granularity in a single Visualization.Level of Detail Expressions provide a way to easily compute aggregations that are not at the level of detail of the visualization.You can then integrate those values within visualizations in arbitrary ways.

Theory/Information:

From Tableau v9 onwards, a new concept called Level of Detail (LoD) expressions has been introduced. LoD expressions can be used to represent the data in different ways at different levels.

When can we use LoD expressions?

Consider using LoD expressions when:

  1. There is a requirement to show the data at a level different from the dimensions/level present in the view
  2. There is a need to obtain some static calculated value that is not affected by any filters that are applied to the view

LoD Expressions in Tableau:

Following is a detailed explanation of LoD expressions in Tableau and how to use them.

Before LOD:

Any calculated field that is created and used in a view, is obtained by performing calculations at the level of the dimensions that are present in the view.

In the below image, we can see that the Level of Detail in the view is ‘Region’ and hence the measure ‘SUM(Sales)’ is calculated at the ‘Region’ level.

2.Region

Now,we have both ‘Region’ and ‘State’ in the view and hence the ‘SUM(Sales)’ is calculated at the ‘Region-State’ level. In this case, if we want to display the ‘SUM(Sales)’ at a ‘Region’ level as well, it is not possible unless we remove ‘State’ from the view. This is where LoDs comes into picture.

1.Region-State

After LOD:

With LoD expressions, we can get both the ‘Region’ level aggregates as well as the ‘State’ level aggregates in the same view.

Syntax:

{FIXED/INCLUDE/EXCLUDE [dim1, dim2,…] : aggregate-expression}

As shown in the above syntax, all LoD expressions need to be enclosed within ‘{ }’. First, the type of LoD calculation is mentioned, followed by the list of dimensions over which the calculation is to be performed and then the actual aggregation calculation that is to be performed. One thing to note is that the result of a LoD expression should always be an aggregated value.

There are 3 types of LoDs that are available. One of these is the first parameter that appears inside the ‘{ }’:

  1. Fixed
  2. Include
  3. Exclude

Let’s see about each type in detail:

2 Fixed:

Fixed’ LoD is used when a calculation needs to be done at the level of a particular dimension, irrespective of what is there in the view.

For example, take a look at the following LoD expression:

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

In the above example, ‘SUM([Sales])’ is always calculated at a ‘Region’ level, irrespective of what dimensions are present in the view.

If you see this image, the LoD expression created above has been placed in the view, along with ‘SUM([Sales])’. If you look at the highlighted columns, we can see that the LoD expression is calculating the Sales at a Region level, whereas the normal Sales measure is calculated at a Region-State level.

3.Fixed LoD

The default LoD expression is ‘Fixed’. Hence, if we do not mention Fixed/Include/Exclude, Tableau will automatically calculate it as ‘Fixed’. If the dimension list is omitted, Tableau performs the LoD calculation for the entire Data Source.

Hence, an LoD expression like (), will always give the maximum of the ‘Order Date’, from the entire data set, at the level of the data in the data source, irrespective of the dimensions in the view.

2 Include

The ‘Include’ LoD expression is used when a particular dimension(s) are to be added to the level of calculation in addition to the dimensions present in the view.

Consider the following LoD expression:

{INCLUDE [State] : SUM([Sales])}

In accordance with the above statement, even if ‘State’ is not part of a view, the calculation, ‘SUM(Sales)’ here, will be calculated at a State-level.

This image below illustrates the difference between the Average(sales) (somewhere between $350 and $250 per category) and the average sale per State (between $15460 and $18200 per category)

Include LoD_1

2 Exclude

Similar to the ‘Include’ LoD expression, ‘Exclude’ LoD expression is used when a particular dimension(s) are to be omitted from the level of calculation, out of the dimensions present in the view.

Consider the following LoD expression:

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

The above statement excludes the Region from the calculation of SUM(Sales).

The below image shows the Sales split by Region and Month of Order Date.Now, if we drop the above created Exclude LoD expression anywhere on the view, say in the Color shelf, then we can see that the calculation that is done excludes the State dimension.

Exclude LoD_1

Let’s see what happens when we drop ‘SUM(Sales)’ on to the color shelf:

If we see the below picture, the minimum value on the color bar is 200 and the maximum value is 41,524. Now, look at the images below. The minimum and maximum values across the Date and Region are 200 and 41,524 respectively. This illustrates that placing SUM(Sales) on the color shelf, considers all the Sales values that appear in the view when both Region and Date are placed in the view.

Exclude LoD_2

Now, let’s see what happens when we place the Exclude expression on the Color shelf:

Exclude LoD_5

If we see the above image, we can see that the minimum value of the Exclude expression is 4,811 and the maximum value is 112,326.

Now, look at the image on the left. Date is the only dimension in the view. There are 2 new calculated fields – Min and Max. They are calculated as follow:

Min:

WINDOW_MIN(SUM([Sales]))

Max:

WINDOW_MAX(SUM([Sales]))

From the below image, it is clear that the minimum and maximum values of Sales at the Date level are 4,811 and 112,326 respectively. Hence, the Exclude expression has not used Region in its calculation .

Exclude LoD_6

Order of execution of filters and LoD Expressions in Tableau:

Below is an image that depicts the order of execution of filters and the LoD expressions that are created in Tableau:

LOD and filters

When comparing with SQL, measure filters are equivalent to the HAVING clause and dimension filters are equivalent to the WHERE clause. Hence, the dimension filters will be applied before the measure filters. Also, the text on the right side of the above image shows where the LoD expressions are calculated. The fixed LoD expressions are calculated before the dimension filters and include/exclude LoD expressions are calculated after the dimension filters.

For example, assume that in your view, the dimension ‘Region’ is present in either the Rows/Columns shelf. Consider the following calculation, for calculating the ratio of sales in a region to the total sales across all the regions:

SUM([Sales]) / ATTR ({FIXED : SUM([Sales])})

Now, if we place ‘Region’ on the Filters shelf and filter out some of the regions, the above calculation will still give the ratio of sales in each region to the total sales across all the regions including the regions that are filtered out. This is because the FIXED LoD expressions are calculated before the dimension filters. Hence, the filter on Region will be applied only after the FIXED LoD expression is calculated.

There are 2 options in order to get the desired results for the LoD expression, after the application of the filter:

  1. Make the ‘Region’ filter to be a context filter, in which case it will be applied before the FIXED LoD expression
  2. Make the LoD expression to be an INCLUDE/EXCLUDE expression, in which case the LoD expression will be calculated after the application of the Dimension filters.

This is all about the LoD expressions in Tableau versions above 9.0. A lot of visualizations can be created using these expressions. These expressions can be used to combine multiple metrics or KPIs in the same worksheet and helps in gaining more meaningful insights.

An another very important concept related to LOD which Tharashasank Davuluru covered in his LinkedIn post.He beautifully explained the LOD concept by taking very simple example

Why LOD functions(INCLUDE,EXCLUDE) are not converting into dimensions but FIXED is converting into dimensions.?

When you save a level of detail expression, Tableau adds it to either Dimensions or Measures.FIXED level of detail expressions can result in measures or dimensions, depending on the underlying field in the aggregate expression. So MIN([Date])} will be a dimension because [Date] is a dimension, and {fixed Store : SUM([Sales])} will be a measure because [Sales] is a measure. When a FIXED level of detail expression is saved as a measure you have the option of moving it to dimensions.INCLUDE and EXCLUDE level of detail expressions are always measures.

Jonathan Sir Comments:

“The distinction is based on the field’s data type, the level of the calculation, and type of LOD expression. Record-level fields that are dates, datetimes, booleans, and strings are by default classified as dimensions. Record-level fields that are numbers are by default classified as measures. FIXED LOD expressions return record level values, so they can end up as a measure or a dimension based on the above logic. So {FIXED [Store] : SUM([Sales])} is not classified as a measure because Sales is a measure, instead it’s because the LOD expression is returning a record-level number. If I write {FIXED [Store]: IF SUM([Sales]) > 100000 THEN 1 ELSE 0 END} then that will be also default to being a measure because it’s returning a number. As you noted the results of a FIXED LOD can be changed into a dimension if need be, which we might want to do with that calculation. Now for aggregates and table calculations: Any calculation that is returning an aggregate result like SUM([Sales])/SUM([Profit]) or a table calculation like LOOKUP(SUM([Sales]),-1) will be classified as a measure no matter the data type. INCLUDE/EXCLUDE LOD expressions are a bit of a special case. They are always placed in the Measures window because they must be aggregated in the view, this is by design for INCLUDE & EXCLUDE LODs. However the results of an LOD expression are available a record-level value in calculations (which must then be aggregated when brought into a view).”

2Points to remember from (Master of LOD – Jonathan Drummey):

  •  Not all data sources support LOD expressions
  •  LOD expressions do not currently support moving totals, running totals, ordinal computations within a partition (LOOKUP, INDEX, etc.), ranking, or anything like PREVIOUS_VALUE so if we need those kinds of computations then we almost certainly need to use table calculations. (Though there are occasional tricks to get around some of these with LODs depending on the data and the exact calculation needed, for example [value] = {FIXED [dimension], MAX([value])} to identify the top/1st value in each partition).
  • If there are large data volumes and relatively few marks then its better to  use table calculations because LOD expressions generate subqueries that can have a big performance impact while table calculations are computed locally only across the marks in the view.
  • If the view has a large number of filters in play then its good to use EXCLUDE LOD expressions and/or table calculations since they are computed later in the pipeline. Since FIXED LOD expressions require context filters to affect them that can a) increase the time it takes to build a view (since all regular filters need to be turned into context filters), b) slow the view down, c) make the view harder to maintain (since every new dimension filter probably needs to be added to the context)

tipsFor more Information ,please refer the below link

Top 15 LOD Expressions by Bethany Lyonshttps://www.tableau.com/LOD-expressions

Tableau online help for LOD expressionshttp://onlinehelp.tableau.com/current/pro/online/windows/en-us/help.html#calculations_calculatedfields_lod.html

Please follow the attached workbook/PDF it will help you to get an insight when exactly LOD need to be used .

The below attached workbook is available in the Tableau Community.

Introduction to Level of Detail Expressions

Chris McClellan LOD Explanation

whitepaper_Level-of-Detail_calcs_LOD

 

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.