Level of Detail Expressions Tableau

Introduction: Level of Detail Expressions Tableau

Level of Details (LOD) expressions was introduced in Tableau 9.0 version to help answer complicated questions easily. At the same time one can specify the exact level of detail right in a calculation without adding fields to view. This and this are good starting point to learn more about Level of Detail expressions.

In summary, as explained in white paper published by Tableau one should understand two key concepts, viz level of detail and calculation level of detail. Viz level of detail is nothing but dimensions placed in row, column, colour, size, label, path or detail shelves. Depending on the dimension chosen by the user, Tableau will aggregate the data accordingly.

LOD (calculation level detail) allows one to determine the levels of detail used in a calculation without actually using these dimensions in the view or visualization that one creates.

A level of detail expression has the following structure:

{[FIXED | INCLUDE | EXCLUDE] <dimension declaration > : <aggregate expression>}

Below is the description of each of the element of LOD expression from Tableau’s online help.

This blog is a first part in series where we will understand LOD expressions one by one with some practical examples. In this blog we will look at “FIXED” LOD expression.

FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view.

Data

To understand how LOD works so that everyone can understand the mechanics of LOD, I decided to 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 answer what the average score is made by all the teams or a specific team by ground on which they have played. We have our data by individual player playing in a match on a given date and on a ground. Logically we want to sum the runs scored by team (i.e. Country) by match date and take the average for a ground. To accomplish this we will use FIXED LOD. Below are the series of steps to answer the question that we have.

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 Ground on Row shelf

Step 4 – Create a calculated field LOD – Runs by Country, Match Date

What we have done here is we have fixed Country and Match Date dimension and calculated sum of runs.

Step 5 – Place the LOD calculated field on Text shelf

Step 6 – Place Runs on Text shelf

We are doing this to understand how LOD expression works in the subsequence steps. So far if you see the LOD expression and our normal measure (Runs) have same value.

Step 7 – Choose Average as aggregation method for LOD calculated field

Step 8 – Choose Average as aggregation method for Runs source measure

Step 9 – Compare the calculated field and source measure values

While the LOD calculated field that we created shows the average runs scored on ground by country, the average aggregation applied on source measure “Runs” depicts the average runs scored by player on ground as our raw data is at player granularity.

Step 10 – Analysis

We can answer lot of questions now by changing the aggregation method for LOD calculated field that we created.

What is the highest score by India on each of the grounds that they have played?

We have applied Country = India filter and changed the aggregation method to maximum for LOD calculated field as shown below along with the answer.

What is the average score by India on each of the grounds that they have played?

We have applied Country = India filter and changed the aggregation method to average for LOD calculated field as shown below along with the answer.

What is the average score by India on each of the grounds against opponents that they have played?

We have applied Country = India filter and placed “Versus” on column and changed the aggregation method to average for LOD calculated field as shown below along with the answer.

This way once the LOD expressions are setup lot of meaningful analysis can be done.

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 websiteto find more details about Tableau and its product offering and features.