LOD Expressions
LOD or Level of Detail expressions computes values at the data source and the visualization level instead of bringing all the data to the Tableau interface. However, LOD expressions give you more control on the level of granularity you want to compute. A simple example is adding dimension to an already calculated aggregate value. You can also use a LOD expression to the table. This is called a Table-Scoped LOD expression.
LOD expressions- Types
There are three types of LOD expressions that can be created in Tableau:
FIXED: FIXED LOD expressions are used to compute a value using the specified dimensions, without any reference to the dimensions in the view.
{FIXED [Region] : SUM([Sales])}
INCLUDE: INCLUDE LOD expressions compute values using the specified dimensions in addition to any dimensions which are in the view. It is used to calculate at a granular level of detail in the database.
{ INCLUDE [Name] : SUM([Sales]) }
EXCLUDE: EXCLUDE LOD expressions declare dimensions to omit from the view level of detail.
{EXCLUDE [Date (Month / Year)] : AVG({FIXED [Date (Month / Year)] : SUM([Sales])})}
Create Calculated Field
1. In the Tableau Desktop, connect to the Excel file named Facebook Data which is saved in the data source.
2. Navigate to the worksheet pane and click on Analysis > Create Calculated Field as shown below.
3. You will notice that a new dialogue box has appeared.
FIXED LOD Expression
For instance, if we want to find the number of Sales for each state in each region. For this, we will use the FIXED LOD function and will create the formula as shown in the following screenshot. Also, change the name to Fixed_Regional_Sales. And click on OK.
From the data pane, under Dimensions drag the Region, and State field to the Rows shelf and the Fixed_Regional_Sales calculated field to the Text shelf under the Marks card. Next, again drag the Region field to the Color shelf. This produces the following outlook, where we can see that the values for the individual states in each region are identical. This is because we have fixed the Region dimension for the calculation of Sales value.
INCLUDE LOD EXPRESSION
For instance, if you want to compute the total sales per customer for the specified dimensions in addition to whatever dimensions are in the view. For this, we will use the INCLUDE LOD function and will create the formula as shown in the following screenshot. Also, change the name to Include_Sales. And click on OK.
The above-calculated field can be applied in the view by dragging it to the column shelf. Also drag the category field to the Rows shelf. It will produce the following view which includes both the dimensions in the calculations where the values would be different for each category.
Exclude LOD Expression
EXCLUDE LOD expressions specify dimensions to exclude from the view level of detail. For example you want to calculate the Sales figure for every month. For this, we will create the formula as shown in the following screenshot. Also, change the name to Exclude_Region. And click on OK.
Drag the above-calculated field and direction field to the columns shelf. Also, drag the year field and drop it under the rows field. You will have the following screenshot:
