Common LOD use cases (customer first purchase, cohorts) in Tableau - Step-by-Step Guide

Introduction

Level of Detail (LOD) expressions in Tableau help you calculate values at different levels of detail than the view. This is useful when you want to find things like a customer's first purchase date or group customers into cohorts based on their first purchase.

When you want to find the first purchase date for each customer regardless of the current view filters.

When you need to create customer cohorts based on the month or year of their first purchase.

When you want to calculate metrics like retention or repeat purchases by cohort.

When you want to compare individual transactions to overall customer behavior.

When you want to fix a calculation at the customer level while showing data at a more detailed level.

Steps

Step 1: Open

- Tableau Desktop and connect to your data source

Your data appears in the Data pane

Step 2: Click

- Analysis menu > Create Calculated Field

A dialog box opens to create a new calculation

Step 3: Type

- Calculated Field dialog

You enter the LOD expression for first purchase date

💡 Use syntax like { FIXED [Customer ID] : MIN([Order Date]) } to get the first purchase date per customer

Step 4: Name

- Calculated Field dialog

You name the field 'Customer First Purchase Date'

💡 Choose clear names to remember what the calculation does

Step 5: Click

- OK button in Calculated Field dialog

The new calculated field appears in the Data pane

Step 6: Drag

- New calculated field to Rows or Columns shelf or Detail on Marks card

You see the first purchase date for each customer in your view

Step 7: Repeat

- Create Calculated Field dialog

You create cohort groups using a calculation like DATEPART('month', [Customer First Purchase Date])

💡 Use this to group customers by the month they made their first purchase

Before vs After

Before

View shows all orders with order dates and customer IDs but no summary of first purchase dates

After

View shows each customer with their first purchase date fixed, enabling cohort grouping and analysis

Settings Reference

FIXED LOD expression

📍 Calculated Field dialog

Calculates a value fixed at the specified dimension level regardless of view filters

Default: None

INCLUDE LOD expression

📍 Calculated Field dialog

Includes additional dimensions in the calculation beyond the view level

Default: None

EXCLUDE LOD expression

📍 Calculated Field dialog

Excludes specified dimensions from the calculation

Default: None

Common Mistakes

Using a regular aggregation like MIN([Order Date]) without FIXED LOD

This calculates the minimum date in the current view, which changes with filters and does not fix per customer

Use { FIXED [Customer ID] : MIN([Order Date]) } to get the first purchase date per customer regardless of filters

Creating cohorts without fixing the first purchase date

Cohorts will be inconsistent because the first purchase date changes with the view

Create a fixed LOD for first purchase date, then use that field to define cohorts

Summary

LOD expressions let you calculate values fixed at a specific level, like customer first purchase date.

Use FIXED LOD to get stable values regardless of filters or view level.

This helps create cohorts and analyze customer behavior over time.

Practice

1. What is the main purpose of using a Fixed Level of Detail (LOD) expression in Tableau for customer analysis?

easy

A. To calculate the total sales for the current view only

B. To find each customer's first purchase date regardless of filters

C. To change the data source connection

D. To create a new data table outside Tableau

Solution

1. Step 1: Understand Fixed LOD expression role

   Fixed LOD expressions calculate values at a fixed granularity, ignoring filters that affect the view.

2. Step 2: Apply to customer first purchase date

   Using Fixed LOD, you can find the earliest purchase date per customer, even if the view filters change.

3. Final Answer:

   To find each customer's first purchase date regardless of filters -> Option B

4. Quick Check:

   Fixed LOD finds fixed values like first purchase [OK]

Hint: Fixed LOD fixes calculation level ignoring filters [OK]

Common Mistakes:

• Confusing Fixed LOD with simple aggregation
• Thinking LOD changes data source
• Assuming LOD creates new tables

2. Which of the following is the correct syntax for a Fixed LOD expression to find the first purchase date per customer in Tableau?

easy

A. { FIXED [Customer ID] : MIN([Purchase Date]) }

B. { INCLUDE [Customer ID] : MAX([Purchase Date]) }

C. { EXCLUDE [Customer ID] : SUM([Sales]) }

D. { FIXED [Purchase Date] : COUNT([Customer ID]) }

Solution

1. Step 1: Identify Fixed LOD syntax

   Fixed LOD uses curly braces with FIXED keyword, then dimension(s), colon, and aggregation.

2. Step 2: Match expression to find first purchase date

   MIN([Purchase Date]) per [Customer ID] finds earliest purchase date per customer.

3. Final Answer:

   { FIXED [Customer ID] : MIN([Purchase Date]) } -> Option A

4. Quick Check:

   Fixed LOD with MIN date per customer = correct syntax [OK]

Hint: Fixed LOD uses { FIXED [Dimension] : Aggregation } [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Using MAX instead of MIN for first purchase
• Fixing on wrong dimension like Purchase Date

3. Given the LOD expression { FIXED [Customer ID] : MIN([Purchase Date]) }, what will be the result if a customer has purchases on 2023-01-10, 2023-02-15, and 2023-03-20?

medium

A. NULL

B. 2023-03-20

C. 2023-02-15

D. 2023-01-10

Solution

1. Step 1: Understand MIN aggregation in LOD

   MIN([Purchase Date]) returns the earliest date for the fixed customer.

2. Step 2: Identify earliest purchase date

   Among 2023-01-10, 2023-02-15, 2023-03-20, the earliest is 2023-01-10.

3. Final Answer:

   2023-01-10 -> Option D

4. Quick Check:

   MIN date per customer = earliest purchase [OK]

Hint: MIN returns earliest date in Fixed LOD [OK]

Common Mistakes:

• Choosing MAX instead of MIN
• Confusing date formats
• Assuming NULL if multiple purchases

4. You wrote this LOD expression to find first purchase date: { FIXED [Customer ID] : MIN([Purchase Date]) }. But the result shows the same date for all customers. What is the most likely error?

medium

A. You forgot to include [Customer ID] in the view or filter context

B. You used MAX instead of MIN

C. You wrote INCLUDE instead of FIXED

D. You used SUM instead of MIN

Solution

1. Step 1: Check LOD expression correctness

   The expression syntax is correct for Fixed LOD with MIN.

2. Step 2: Understand why all customers show same date

   If [Customer ID] is not in the view or filter, Tableau aggregates all customers together, showing one date.

3. Final Answer:

   You forgot to include [Customer ID] in the view or filter context -> Option A

4. Quick Check:

   Missing dimension in view causes same value for all [OK]

Hint: Always include LOD dimension in view to see distinct results [OK]

Common Mistakes:

• Changing aggregation instead of checking view
• Confusing FIXED with INCLUDE
• Ignoring filter context effects

5. You want to create customer cohorts by their first purchase month using LOD expressions. Which approach correctly assigns each customer to their cohort month?

hard

A. { EXCLUDE [Customer ID] : COUNT([Customer ID]) }

B. { INCLUDE [Customer ID] : MAX([Purchase Date]) }

C. { FIXED [Customer ID] : DATETRUNC('month', MIN([Purchase Date])) }

D. { FIXED [Purchase Date] : MIN([Customer ID]) }

Solution

1. Step 1: Identify cohort definition

   Cohorts group customers by their first purchase month, so we need first purchase date truncated to month.

2. Step 2: Use Fixed LOD to get first purchase month per customer

   Fixed LOD with MIN([Purchase Date]) finds first purchase date; DATETRUNC('month', ...) converts it to month start.

3. Final Answer:

   { FIXED [Customer ID] : DATETRUNC('month', MIN([Purchase Date])) } -> Option C

4. Quick Check:

   Fixed LOD + DATETRUNC for cohort month = correct [OK]

Hint: Use DATETRUNC with Fixed LOD for cohort month [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Not truncating date to month
• Fixing on wrong dimension