Skip to main content

Level of Detail Expressions in Tableau

Types of LOD expressions

There are three types of LOD expressions you can create in Tableau:
  • FIXED
  • INCLUDE
  • EXCLUDE
You can also scope an LOD expression to the table. This is called a Table-Scoped LOD expression.

FIXED

FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view.

Example

The following FIXED level of detail expression computes the sum of sales per region:
{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 dimension referenced in the calculation, which in this case is Region. Because of this, you can see that the values for the individual states in each region are identical. For more information about why this happens, see Aggregation and Level of Detail Expressions.
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]) when determining values for the expression. The result would be as follows:

INCLUDE

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.

Example 1

The following INCLUDE level of detail expression computes total sales per customer:
{ INCLUDE [Customer Name] : SUM([Sales]) }
When that calculation is placed on the Rows shelf, aggregated as AVG, and the [Region] dimension is placed on the Columns shelf, the view shows the average customer sales amount per region:
If the [Sales] measure is then dragged to the Rows shelf, the result illustrates the difference between the total sale for each region and the average sale per customer for each region:

Example 2

The following INCLUDE level of detail expression calculates sum of sales on a per-state basis:
{ INCLUDE [State] : SUM(Sales)}
The calculation is placed on the Rows shelf and is aggregated as an average. The resulting visualization averages the sum of sales by state across categories.
When Segment is added to the Columns shelf and the calculation is moved to Label, the LOD expression results update. Now you can see how the average sum of sales per state varies across categories and segments.

EXCLUDE

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).

Example 1

The following EXCLUDE level of detail expression computes the average sales total per month and then excludes the month component:
{EXCLUDE [Order Date (Month / Year)] : AVG({FIXED [Order Date (Month / Year)] : SUM([Sales])})}
For more information on creating Month / Year date fields, see Custom Dates.
Notice that this is a nested level of detail expression—that is, a level of detail expression within another level of detail expression.
Saved as [average of sales by month], the calculation can then be subtracted from the sum of sales per month by means of an ad-hoc calculation on the Rows shelf:
With Month([Order Date]) on the Columns shelf, this creates a view that shows the difference between actual sales per month over a four-year period and the average monthly sales for the entire four-year period:

Example 2

The following level of detail expression excludes [Region] from a calculation of the sum of [Sales]:
{EXCLUDE [Region]: SUM([Sales])}
The expression is saved as [ExcludeRegion].
To illustrate how this expression might be useful, first consider the following view, which breaks out the sum of sales by region and by month:
Dropping [ExcludeRegion] on Color shades the view to show total sales by month but without the regional component:

Table-Scoped

It is possible to define a level of detail expression at the table level without using any of the scoping keywords. For example, the following expression returns the minimum (earliest) order date for the entire table:
{MIN([Order Date])}
This is equivalent to a FIXED level of detail expression with no dimension declaration:
{FIXED : MIN([Order Date])}

LOD expression syntax

Level of Detail Expression Syntax
A level of detail expression has the following structure:
{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}
The elements in a level of detail expression are described in the following table.
ElementDescription
{ }The entire level of detail expression is enclosed in curly braces.
[FIXED | INCLUDE | EXCLUDE]
The first element after the opening curly brace is one of the following scoping keywords:
  • FIXED
    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 ignore all the filters in the view other than context filters, data source filters, and extract filters.
    Example: { FIXED [Region] : SUM([Sales]) }
    For more information about FIXED level of detail expressions, and for some example FIXED level of detail scenarios, see the FIXED section.
  • INCLUDE
    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 are most useful when including a dimension that isn’t in the view.
    Example: { INCLUDE [Customer Name] : SUM([Sales]) }
    For more information about INCLUDE level of detail expressions, and for some example INCLUDE level of detail scenarios, see the INCLUDE section.
  • EXCLUDE
    EXCLUDE level of detail expressions explicitly remove dimensions from the expression—that is, they subtract dimensions from the view level of detail.
    EXCLUDE level of detail expressions are most useful for eliminating a dimension in the view.
    Example: {EXCLUDE [Region]: SUM([Sales])}
    For more information about EXCLUDE level of detail expressions, and for some example EXCLUDE level of detail scenarios, see the EXCLUDE section .
  • Table-Scoped
    In the case of a table-scoped level of detail expression, no scoping keyword is required. For more inforamtion, see the Table-Scoped section.
<dimension declaration>
Specifies one or more dimensions to which the aggregate expression is to be joined. Use commas to separate dimensions. For example:
[Segment], [Category], [Region]
For level of detail expressions, you can use any expression that evaluates as dimension in a dimensionality declaration, including Date expressions.
This example will aggregate the sum of Sales at the Year level:
{FIXED YEAR([Order Date]) : SUM(Sales)}
This example will aggregate the sum of Sales for the [Order Date] dimension, truncated to the day date part. Because it is an INCLUDE expression, it will also use the dimensions in the view to aggregate the value:
{INCLUDE DATETRUNC('day', [Order Date]) : AVG(Profit)}
Note: It is strongly recommended that you drag fields into the calculation editor when creating dimension declarations, instead of typing them. For example, if you see YEAR([Order Date]) on a shelf and then type that as the dimension declaration, it will not match the field on the shelf. But if you drag the field from the shelf into the expression, it will become DATEPART('year', [Order Date]), and that will match the field on the shelf.
With named calculations (that is, calculations that you save to the Data pane, as opposed to ad-hoc calculations, which you do not name), Tableau cannot match the name of a calculation to its definition. So if you create a named calculation, MyCalculation, defined as follows:
MyCalculation = YEAR([Order Date])
And then you created the following EXCLUDE level of detail expression and used it in the view:
{EXCLUDE YEAR([Order Date]) : SUM(Sales)}
Then MyCalculation would not be excluded.
Similarly, if the EXCLUDE expression specified MyCalculation:
{EXCLUDE MyCalculation : SUM(Sales)}
Then YEAR([Order Date]) would not be excluded.
:A colon separates the dimension declaration from the aggregate expression.
<aggregate expression>The aggregate expression is the calculation performed to define the target dimensionality.

Labels:

Comments

Post a Comment

Popular Posts

Add Space between bars in Tableau chart

Add Space between bars in Tableau chart Scenario: Tableau defaults to no spacing between the panes in a view. How do I get some spacing between groups of bars in my charts? It can be achieved through the steps below: Add subtotals (Analysis->Totals->Add All Subtotals) Right click on the measure pill on the Rows shelf and change the default SUM() aggregations to MIN() To edit the color legend – double click on the Total color, this takes you to a color dialog, select the WHITE color, click OK Right click on the word Total in the X axis and select Format In the Format window, click the Header tab and blank out the Total label field The added space looks like below:
1 comment
Read more

Adding “Apply” Button to A Filter Menu In Tableau

Adding An “Apply” Changes Button To A Filter Menu In Tableau Sometimes when a user is changing the options they want on a filter in Tableau, the chart updates as they change each option. This might not be the best user experience if they are changing many options and don’t want the dashboard to redraw the view until they have completed their selection. It is very simple to fix this – Tableau includes an “apply” changes option, which when enabled, means the dashboard won’t redraw to reflect the new filter choices until the user presses the “apply” button. Simply click the drop down arrow on the filter menu > go to “customize”, then click “show apply button”.  
1 comment
Read more

Error "Cannot mix aggregate and non-aggregate arguments with this function"

Error "Cannot mix aggregate and non-aggregate arguments with this function" When Creating a Calculated Field When creating a calculation, the following error might occur:  "Cannot mix aggregate and non-aggregate arguments with this function." The issue can be resolved through one of the below options: Modify the calculation so that all fields are either aggregate or non-aggregate. Each option can result in different values (please reference the additional information section for specific examples). Option 1 (Aggregate Then Divide) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales]) Option 2 ( Divide Then Aggregate) Remove aggregations from all of the fields. Sample: [Profit] / SUM ([Sales]) ->[Profit] / [Sales]   Option 3 ( Condition Then Aggregate) Move the aggregation so all fields are aggregated. For example, the calculation: IF [Row ID] = 1 THEN SUM( [Sales] ) END  could...
Post a Comment
Read more

Tableau Bar Chart Rounded corners

Tableau Bar Chart Rounded corners Follow the steps in the video below to create rounded corners for the bar chart:
Post a Comment
Read more

Removing "Abc" Placeholder Text in Tableau Measures column

Removing "Abc" Placeholder Text in Tableau Measures column Quick and easy fix for this issue could be: Use Polygon mark type On the Marks card in the dropdown menu, select Polygon Resize the last column to make it smaller Navigate to Format > Borders In the left-hand Format Border Pane, for Column Divider, For Pane, select None from the dropdown menu Navigate to Format > Shading In the left-hand Format Border Shading, for Row Banding, move the slider to the desired level of row banding
2 comments
Read more

Using Special Characters in Tableau URL Parameters

Using Special Characters in Tableau URL Parameters When we try to use special characters in URL parameters, the URL parameter might not do anything, or an error might occur. The issue can be resolve by:  Use one of the following workarounds: Replace the special character with the URL encoding sequence for backslash (\) (%5c) followed by the URL encoding sequence for the special character. The backslash is needed to escape the special character. For example, the URL encoding sequence for backslash and comma (\,) is %5c%2c.  In the data source, separate comma-delimited field values into separate columns that can be filtered independently. In Tableau Desktop, use a calculated field to replace the special characters, such as commas or spaces, with hyphens (-). Cause The browser cannot parse the special characters used in the URL. Additional Information The error varies depending on the browser and special character being used. Networ...
1 comment
Read more

Cannot mix aggregate and non-aggregate arguments with this function

Cannot mix aggregate and non-aggregate arguments with this function Issue When creating a calculation, one of the following errors might occur:    "Cannot mix aggregate and non-aggregate arguments with this function." (Option 1,2,3 or 4 can be used). " All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources." (Option 1 or 3 can be used). "Argument to sum (an aggregate function) is already an aggregation, and cannot be further aggregated ."  (Option 2, 3 or 4 can be used). Environment Tableau Desktop Resolution Each option can result in different values (please reference the attached workbook in the right-hand pane and additional information section for specific examples). Option 1 (Aggregate All Fields) Wrap all fields in an aggregation. Sample: [Profit] / SUM ([Sales]) -> SUM ([Profit]) / SUM ([Sales])   Option 2 ( De-aggregate All Fields) Remove aggregations from all of ...
Post a Comment
Read more

Set a Date Filter Default to Max Date in Tableau

Set a Date Filter Default to Max Date in Tableau Some of us might have not noticed the setting a   filter to default to the Max date. There is a quick and simple option to achieve this in Tableau filters. Follow the steps below: 1. Create a date filter with the date dimension. 3. Drag the date dimension to filters pane. 4. Edit the date filter to Select from the list and select the max date available. 2.    Now, y ou have the option  'Filter to latest date value when workbook is opened', check this box and click on OK.
1 comment
Read more

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects)

Tableau Extract Update Error "Timeout Error: IPC_NamedPipe::Select(WaitForMultipleObjects) When trying to update the Data Source or w hen attempting to connect to a data source, or create and/or refresh extracts with Tableau Desktop, the following error message occurs:  IPC_NamedPipe::Select(WaitForMultipleObjects): Timeout. Cause Anti-virus software is blocking Tableau processes from running, or the Logs folder contained corrupted information.  Resolution Option 1: If it is easy w ork with IT helpdesk/support to add below Tableau folders and processes to exclusions : Tableau.exe hyperd.exe hyperdstarter.exe *.hyper extension (if AntiVirus has an option for extension exclusions) C:\Users\<username>\Documents\My Tableau Repository C:\Users\<username>\AppData\Local\Temp\TableauTemp C:\Users\<username>\AppData\Local\Tableau C:\Program Files\Tableau Make sure that child folders are included in the exclusions. AntiVirus prog...
Post a Comment
Read more