Tableau – LOD Calculations

Tableau is a commercially available software used in business intelligence to visualize data interactively and understand and deal with it better. It is used for data analysis to finally help draft plans or inferences a company may need to understand themselves.

LOD stands for the level of detail and it is just a mechanism supported by tableau to implement complex queries which have always been done using a Structured Query Language. Tableau helps achieve same level granularity between dimensions and can be easily applied by someone who has no idea about any Structured Query Language but understands their data needs well.

Syntax:

{ <LOD type> <dimension> : <calculation>}

There are three types of LOD supported:

  • Exclude: This LOD type is used to exclude specified dimension from the view.
  • Include: This LOD type is used to include a specified dimension along with other dimensions within the view.
  • Fixed: Unlike exclude and include, fixed doesn’t depend on what is on the view rather focuses more on calculations. Thus it produces a fixed value for the specified dimensions.

Exclude 

This LOD type is used to exclude specified dimension from the view. Given below is an implementation with proper steps to achieve exclude LOD:

Query:

For a sub-category, the element gets total sales of the category it belongs to. Note that the query doesn’t ask for sub-category sale but the total sale of the category it belongs to, thereby, excluding the individual sales.

  • Start application
  • Connect data
  • Move the required dimensions. In the given example category is moved to rows and sales is moved to reflect the respective sales.
  • Add sub-category dimension to rows to see what initial data looks like:
  • Add a query to exclude: select downward arrow given along sub-category.
  • A dialog box appears:
  • Select Create–> Calculated field
  • A dialog box will appear:
  • Add the name of the query and query and select OK.
  • The name of the query will appear in the list of dimensions on the left of the sheet.
  • Drag exclude and drop it to your table.

Include

This LOD type is used to include a specified dimension along with other dimensions within the view. Given below is an implementation with proper steps to achieve include LOD:

Query:

Get average of the manufacturer sales as well as average sales for each category. In this average manufacturer, sales will be included within the dimensions in the view.

  • Start application
  • Connect data
  • Add the required dimension. Here category, sub-category and sales.
  • For including manufacturers average sale- select the downward arrow given in front of the manufacturer dimension.
  • A dialog box appears:
  • Select create–> Calculated field
  • A dialog box appears:
  • Add the name of the query and the query to include. Select OK.
  • Here we have taken SUM(sales) for manufacturer sales for each category and named this query as include.
  • Add include to view
  • Select the average option to get average.
  • Resultant data gives an average of sales as well as the average of manufacturer sales that can be used for further analysis.

Fixed

Unlike exclude and include, fixed doesn’t depend on what is on the view rather focuses more on calculations. Thus, it produces a fixed value for the specified dimensions.

Query:

Get the maximum profit each manufacturer has earned for each product. Here the maximum value for the profit will be fixed for various manufacturers. A manufacturer is capable of producing more than one product for such cases the maximum profit will be produced irrespective of the product.  

  • Start application
  • Connect data
  • Move the required dimensions. Here, category, sub-category and manufacturer is moved to rows.
  • Select the downward arrow given in front of the manufacturer
  • Select Create–>  Calculated field
  • A dialog box will appear:
  • Add a query to create a required query, select OK.
  • Fixed IOD will be visible on left of the sheet along with other dimensions, drag and drop it to the sheet
  • To analyze for a single manufacturer, right-click on the manufacturer and select keep the only option.
  • The result will look like this:
Follow Us On