_edited_edited.png)
Tableau is a widely used data visualization and analytics software that allows users to create interactive and visually appealing dashboards, reports, and presentations from their data. With Tableau, users can connect to various data sources, including databases, spreadsheets, cloud services, and web data connectors, to analyze and visualize their data in real-time.
Tableau's Level of Detail (LOD) calculations are a powerful feature that allows users to perform calculations at different levels of granularity within their data.
LOD calculations provide users with flexibility in defining the level of granularity at which calculations are performed. This enables users to analyze data at various levels of detail, such as overall, by category, or by specific dimensions.
LOD calculations are useful in a variety of scenarios, such as:
Calculating aggregates at a different level of detail than the visualization.
Comparing values against different levels of granularity.
Performing complex analyses that require aggregating data across multiple dimensions.
Types of Level of Detail(LOD) Expression
LOD expressions enable computation of values at both the data source and visualization levels. It allows the aggregation of data at specified dimension level.
Moreover, they offer greater control over the granularity of computation.They can be executed atÂ
a more detailed level (INCLUDE),Â
a less detailed level (EXCLUDE),Â
a completely independent level (FIXED).
LOD expression syntax
A level of detail expression has the following structure:
{ [FIXED | INCLUDE | EXCLUDE] <dimension declaration> : <aggregate expression>}
{ }
The entire level of detail expression is enclosed in curly braces.
INCLUDE LOD expression
An Include Level of Detail (LOD) expression in Tableau allows users to calculate values while including specific dimensions in the calculation, while still maintaining the visualization's level of detail.
Expressions at the INCLUDE level of detail calculate values using the specified dimensions in conjunction with any dimensions present in the view.
It can be useful when you want to calculate at a fine level of detail in the database.
Fields based on INCLUDE level of detail expressions change as you add or remove dimensions from the view.
For example, consider creating a visualization showing Average Sales / Region. Though visualization contains only Category and Sub-Category fields as dimensions, we are able to show a finer level of detail(Region) in the visualization.
Create a calculated field as shown below.
Drag and drop category and subcategory fields to Rows.
Drag and drop sales to columns. Again drag and drop sales to columns. Change the measure to Average for the second sales.
Drag and drop Avg Sales / Region calculated field to columns. Change the measure to Average.
Avg(Sales)Â - Average is computed at the subcategory level.
Avg(Sales/Region) - Average is computed at the Region level of detail.
EXCLUDE LOD expression
An Exclude Level of Detail (LOD) expression in Tableau allows users to calculate values while excluding specific dimensions from consideration, while still maintaining the visualization's level of detail.
With an Exclude LOD expression, you specify one or more dimensions that you want to exclude from the calculation. These dimensions are omitted from the calculation process, meaning that the calculation is performed as if those dimensions were not part of the data.
EXCLUDE cannot be used in row-level calculation as there is no row to omit.
Syntax : {EXCLUDE <Dimension declaration>: <Aggregate operation>}
For example consider calculating Percent of sales amount contributed by each segment to the total sales.
Create a calculated field as shown below.
Drag and drop Segment to Rows. Drag and drop sales to columns.
Drag and drop the calculated field to columns and to label. Change the format of the calculated field to percentage.
FIXED LOD Expression
A Fixed Level of Detail (LOD) expression in Tableau calculate values using the specified dimensions independently of the view's level of detail, meaning they are unaffected by other dimensions present in the view. Unlike the Include LOD expression, which includes specific dimensions in the calculation, and the Exclude LOD expression, which excludes specific dimensions, the Fixed LOD expression ignores the visualization's level of detail entirely. Instead, it calculates values based solely on the fixed dimensions specified in the expression.
FIXED level of detail expressions ignore all the filters in the view other than context filters, data source filters, and extract filters.
Syntax : {FIXED <Dimension declaration>: <Aggregate operation>}
For example, consider finding the count of products whose sales price is greater than 500 or less than 500.
Create a calculated field as shown below.
Drag and drop subcategory to Rows.
Drag and drop calculated field ‘Product aggregation’ to columns. Change the measure from sum to count()
LOD at data source level { }
Level of detail calculations allow us to create dynamic titles in our visualization.
For example, consider finding the total number of customers in each city. Display the same in the title.
Create a calculated field as shown below.
Drag and drop customer ID in Rows and Sales into columns.
Drag and drop City to Filer.
Drag and drop calculated field(No of customers) and city to detail.
Double click to edit title and add the city and calculated field to the title.
Select the small drop down icon in the City field in Filters. Select ‘Add to context’ .
While LOD calculations offer powerful analytical capabilities, they can impact performance, especially when working with large datasets. It's essential to use LOD calculations judiciously and optimize their performance where possible.
Overall, Tableau's LOD calculations provide users with a flexible and powerful tool for analyzing data at different levels of granularity, enabling deeper insights and more sophisticated analysis.
留言