Level of Detail Expressions – Part 2

This is a second part in a series of 3 blogs focussed on Level of Details (LOD) expression introduced in Tableau 9.0 version. In the first part we looked at the one of the variants of LOD expressions which is using FIXED scoping keyword. In this part we are going to create LOD expression using EXCLUDE scoping keyword. Before we proceed ahead with the details we will understand the mechanics of EXCLUDE LOD expression.

The below graphic explains what happens in terms of Aggregation and Granularity for EXCLUDE LOD expression. Tableau first removes the excluded dimension from the Viz LOD and performs the calculation as if the dimension was not present at all. Then the value computed by LOD gets duplicated for each member of the dimension(s) that was excluded from the LOD expression.

Data

To understand how EXCLUDE LOD expression works so that everyone can understand its mechanics, we will use ODI Cricket batting data that we have already looked at before in earlier blogs.

For the benefit of new readers we will quickly understand the structure of the data and dimensions and measures therein.

The data has been sourced from howstat.com and formatted appropriately for Tableau’s consumption. This is the first important and often time-consuming

Step before data visualization and exploration can happen. We have batting data for One Day International (ODI) matches played between years 1971 to 2011 with close to 60,000 data points. The below table gives you a quick overview of important dimensions and measures present in the dataset.

As usual we will start with asking the question that we want to answer using LOD expression.

Let us say we want to analyse the batting average and average score rate of the GOD of cricket Sachin Tendulkar over the years and against the opposition that he has played. We also want to visually see how the batting average and average score rate for every year and opposition combination compares against his career average (both batting and score/strike rate). Career average is the overall average and it is not the average specific to any year and/or opposition.

Data Exploration and Analysis

Step 1 – Connect to the data.

Open the Data worksheet from text file that has the data.

Step 2 – Go to Sheet 1 and analyse/review the loaded data

Step 3 – Place Versus on Row shelf

Step 4 – Place Match Date on Column shelf

Step 5- Put Player = Sachin Tendulkar on Filter

Step 6 – Create a parameter to toggle between Runs and Score Rate

Step 7 – Create a Calculated Field based on Parameter

Notice that we haven’t chosen any aggregation for Runs or Score Rate. We will be using this calculated field in the LOD expression that we will create in subsequent Steps.

Step 8 – Choose to display Parameter Control

Step 9 – Put Calculated field on Size shelf and choose Average as aggregation method

Toggling the parameter between Runs and Score Rate using the parameter control shows the Batting Average or Average Score Rate of Sachin Tendulkar by Year against each opponent.

Step 10 – Create EXCLUDE LOD expression

In this Step we have created an EXCLUDE LOD expression which calculates the Batting average or Average Score Rate of Sachin Tendulkar’s career as we have instructed Tableau to exclude Versus and Match date VIZ LOD dimensions and calculate the average of the metric (runs or score rate) selected through parameter control.

Step 11 – Put LOD calculated field on tooltip

To understand how EXCLUDE LOD expression works; put the LOD calculated field and calculated field created on top of parameter on Text shelf and convert the visualization into table.

Step 12 – Create a calculated field to compare VIZ LOD average with career average

Step 13 – Put Above/Below Career Average field on Colour shelf

Step 14 – Give Final touches

Change colour scheme.

Exclude opponents against which Sachin has not played that frequently.

Here is how the final output looks like.

Now it is analysis time.

Looking at the visualization below it is evident that Sachin slowed down in terms of score rate post 2004 when compared to its career score rate.

Also against West Indies and South Africa, Sachin could never match to his career score rate.

When it comes to batting average, Sachin has been fairly consistent over the years and against each opposition.

Though between 2004 and 2008 against Sri Lanka, Sachin was consistently below his career batting average.

Stay tuned for more learning through visualization with Tableau.

About Tableau

Tableau (NYSE:DATA) headquartered in Seattle, Washington has a mission to help people see and understand data. It offers a product portfolio for data visualization focused on business intelligence.

One can visit the official Tableau website to find more details about Tableau and its product offering and features.