Share with your network!

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.

Aggregation and  Granularity for EXCLUDE 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.

DimensionsMeasures

Country

Runs

Player name

Score Rate (runs per 100 balls faced)

Opponent country

 

Ground

 

Match Date

 

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.

connecting data to tableau

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

analyzing data in tableau

Step 3 – Place Versus on Row shelf

steps of LOD expressions in Tableau

Step 4 – Place Match Date on Column shelf

Steps of LOD expressions in  Tableau

Step 5- Put Player = Sachin Tendulkar on Filter

filtering data in tableau

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

creating parameters in  tableau

Step 7 – Create a Calculated Field based on Parameter

creating calculated field in  Tableau

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

Steps of LOD expressions in  Tableau

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

Steps of LOD expressions in  Tableau

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

creating EXCLUDE LOD  expression in Tableau

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.

Steps of LOD expressions in  Tableau

Steps of LOD expressions in  Tableau

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

Steps of LOD expressions in  Tableau

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

Steps of LOD expressions in  Tableau

Step 14 – Give Final touches

Change colour scheme.

Exclude opponents against which Sachin has not played that frequently.

Steps of LOD expressions in  Tableau

Here is how the final output looks like.

Steps of LOD expressions in  Tableau

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.

LOD analysis in Tableau

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.

LOD analysis in Tableau

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.