_edited_edited.png)
Tableau – LOD Expressions
Tableau is a visualization tool that has been created to simplify data-driven decision making. With that being said, we can create different charts using various dimensions and measures in order to analyze the data of our data source. For analyzing data at a more detailed level, Tableau 9.0 introduced the syntax for LOD expressions.
Aggregation Vs Granularity
Before learning about LOD expressions, let us understand about aggregation and granularity.
While creating visualization using a large dataset, by default the ‘measures’ added to the view will be aggregated by Tableau. Sum, average, median are common aggregations for measures. ’Dimensions’ can also be aggregated to Minimum, Maximum, Count, Count Discrete.
Granularity of data means how detailed the data is. It refers to the level of detail or precision of the data. High level of granularity will have a greater number of individual pieces of information whereas low level of granularity means a smaller number of individual pieces of information.
Aggregation and Granularity are inversely proportional to one another. The higher the level of aggregation, lower the level of granularity. Aggregations are ‘Table Calculations’ that are local to the worksheet. Granularity level details can be attained with ‘Calculated Fields’ that are saved as separate column in the data source.
LOD Expressions
Level Of Detail (LOD) expressions allows us to compute values at both the data source level and visualization level. LOD expressions allows to build visualization and bring on metrics on a level that are not shown on visualization.
There are three types of LOD expressions:
Fixed
Independent of the view
Exact level of detail
Include
Adds to view
Lower level of detail
Exclude
Minus from the view
Higher level of detailed
Syntax
The syntax of the LOD expression is:
{ Type [Dimension(s)]: Aggregate }
Type represents the type of LOD expression. It can be ‘INCLUDE’, ‘FIXED’, ‘EXCLUDE’.
[dimension(s)] is optional. This represents the dimension on which the LOD expression is calculated. Either one dimension or multiple dimensions can be specified.
Aggregate is the aggregation of measure. It can be sum, average, count, and so on.
The LOD expression is enclosed in the curly braces.
Fixed LOD expression
Fixed LOD expression doesn’t depend on what is shown in the view. This level of detail only looks at the expression for granularity, that is what dimensions and aggregates are given in the expression. The result of Fixed LOD is scalar.
We can see with an example on how to create a fixed LOD expression.
From Tableau connect to the data source, superstore.xls.
Drag the ‘Region’ to columns shelf and ‘Segment’ to rows shelf.
Drop ‘Sales’ measures to ‘Text’ under marks shelf.
We can view the Segment wise sales for different regions.
Let us assume that we need the region wise sales to be fixed, irrespective of other dimensions in the view, we can write a Fixed LOD expression.
To do so,
Click on ‘Analysis’ from the menu bar and select ‘Create Calculated Field’.
Let us name it ‘RegionWiseSales’ and enter the calculation as
{FIXED [Region]:SUM[Sales]}
Now ‘RegionWiseSales’ measure will be created.
Drag this to ‘Text’ under marks shelf.
New rows will be added to view representing the fixed region wise sales. (The rows that are highlighted.)
Let us add the total by double click on ‘Total’ in the Analytics tab.
Even if you add or remove dimensions to the view, the ‘RegionWiseSales’ LOD will not get affected. This will remain the same. Hence this always looks only at the expression and the not the view.
Also, the dimension filters have no effect on Fixed LOD expressions. Let us add ‘Segment’ to the filters and deselect ‘Consumer’. Now the ‘Consumer’ sales will not appear in the view. But our LOD expression will not get affected.
As we can see, the highlighted row shows the total sales according to the applied filter. But ‘RegionWiseSales’ is still fixed by including all the regions as mentioned in our LOD calculation.
Include LOD expression
Include LOD expressions will add more granularity to the view. This will take into the account what are all dimensions in the view and also what are all included in the expression. This type of LOD expressions gives more detail from the view. Hence these expressions are less aggregated. The result of Include LOD expression is an ATTR.
Let us create a use case to write an include LOD expression.
Using Tableau view, create a table chart for sum of ‘Sales’ based on ‘Category’ and ‘Sub-category’. To achieve this:
Drag ‘Category’, ‘Sub-category’ to the rows shelf.
Drop ‘Sales’ to the ‘Text’ in marks shelf.
Now sales per region is displayed as table chart.
Select ‘Analytics’ tab and double click ‘Totals’.
Click the drop down from SUM(Sales) in the marks shelf.
Click on ‘Total using’ and select ‘Average’.
The marked totals in the screenshot below is the average sales per category based on sub-category data.
With the help of Include LOD expression, we can create a category wise sale, that also includes the average sales per category. So, now let us do this.
Drag ‘Category’ to rows shelf.
Drop ‘Sales’ to ‘Text’ in the marks shelf
Create a calculated field by selecting ‘Create Calculated Field’ from ‘Analysis’ in the menu bar.
Name it ‘Average Sales’ and add the calculation as:
AVG({ INCLUDE [Sub-Category]:SUM([Sales])})
‘Average Sales’ measure is created.
Drag ‘Average Sales’ to rows shelf.
From ‘Show Me’ select the table chart.
Select ‘Analytics’ tab and double click ‘Totals’.
Now we can see that average sales is included to the view along with other dimensions. Include LOD expressions provide further detail about the sales (the Average Sales).
Dimension filters have precedence over include LOD expressions. For instance, add ‘Sub-category’ to the filters, then show filter and select all to view the chart that we just created. Now deselect any ‘Sub-category’ (suppose we deselect Binders) and observe the ‘Average Sales’ shows the change.
Exclude LOD expression
Exclude LOD expressions are exactly the opposite to Include LOD expressions. This will help us to subtract details from the view. This will take into the account what are all dimensions in the view and exclude the dimensions mentioned in the expression. The dimensions and measures are more aggregated with this type of LOD expressions. Hence the granularity is lesser. The result of Exclude LOD expression is an ATTR.
Let us detail how to create an Exclude LOD expression in Tableau and how it works.
Drag ‘Category’, ‘Sub-category’ to the rows shelf.
Drop ‘Sales’ to ‘Text’ in the marks shelf.
Create a calculated field by selecting ‘Create Calculated Field’ from ‘Analysis’ in the menu bar.
Name it ‘Exclude Sub-category’ and add the calculation as:
{ EXCLUDE [Sub-Category]:SUM([Sales])}
‘Exclude Sub-category’ measure is created.
Double click on ‘Exclude Sub-category’ measure.
The sum of ‘Sales’ for the sub-categories is removed.
Similar to Include, Exclude LOD expressions also will get affected by the dimension filters.
Conclusion
LOD expressions helps in increasing or decreasing the granularity of the view. There are so many uses for LOD expressions like dealing with duplicates, get a single aggregate, isolate specific values, turn rows into measures and so on. Based on the needs of details to be included in our analysis, the three types of LOD expressions can be used in various ways.
Thanks for reading!!!