Common LOD use cases (customer first purchase, cohorts) in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Customer First Purchase Date Using LOD Expression

You have a sales dataset with fields: CustomerID, OrderDate, and SalesAmount. Which Tableau LOD expression correctly calculates the first purchase date for each customer?

A { INCLUDE [CustomerID] : MAX([OrderDate]) }

B { FIXED [CustomerID] : MIN([OrderDate]) }

C { EXCLUDE [CustomerID] : MIN([OrderDate]) }

D MIN([OrderDate])

💡 Hint

Think about fixing the calculation at the customer level to get their earliest order date.

The FIXED LOD expression fixes the calculation at the CustomerID level and returns the minimum OrderDate, which is the first purchase date for each customer.

❓ visualization

intermediate

Best Visualization for Customer Cohort Analysis

You want to visualize customer retention by cohorts based on their first purchase month. Which visualization type is best suited for this purpose?

A Pie chart showing total sales by product category

B Bar chart showing total sales per region

C Line chart showing number of customers retained over months by cohort

D Scatter plot of sales amount vs. order count

💡 Hint

Cohort analysis tracks groups over time, so a time series visualization is best.

A line chart showing retention over time by cohort clearly shows how each group of customers behaves month by month.

❓ data_modeling

advanced

Designing Data Model for Cohort Analysis

You want to perform cohort analysis by customer first purchase month and track their monthly retention. Which data model design is best to support this in Tableau?

A A star schema with a customer dimension table and a fact sales table including OrderDate

B Separate tables for customers and orders without relationships

C A single fact table with CustomerID, OrderDate, and a calculated FirstPurchaseDate field

D Only an aggregated table with monthly sales totals

💡 Hint

Think about how to relate customer info with their orders efficiently.

A star schema with a customer dimension and fact sales table allows easy calculation of first purchase and cohort grouping.

🎯 Scenario

advanced

Using LOD to Calculate Cohort Retention Rate

You have a dataset with CustomerID, OrderDate, and SalesAmount. You want to calculate the retention rate for each monthly cohort (based on first purchase month) for subsequent months. Which approach using Tableau LOD expressions is correct?

A Use a simple SUM aggregation of sales amount by month

B Use INCLUDE LOD to calculate average sales per month without cohort grouping

C Use EXCLUDE LOD to remove customer dimension and sum sales by month

D Use FIXED LOD to get each customer's first purchase month, then count customers with orders in each month divided by cohort size

💡 Hint

Retention requires knowing the cohort and tracking customers over time.

FIXED LOD fixes the first purchase month per customer, enabling cohort grouping. Then counting customers active in each month divided by cohort size gives retention rate.

🔧 Formula Fix

expert

Identify the Error in This LOD Expression for First Purchase Date

You wrote this Tableau LOD expression to get the first purchase date per customer: { FIXED [CustomerID] : MIN(OrderDate) }. However, Tableau returns an error. What is the cause?

Tableau

{ FIXED [CustomerID] : MIN(OrderDate) }

A OrderDate is missing brackets and should be [OrderDate]

B FIXED LOD cannot use MIN aggregation

C CustomerID should be aggregated inside the expression

D The expression must use INCLUDE instead of FIXED

💡 Hint

Check the syntax for field references in Tableau LOD expressions.

In Tableau, field names must be enclosed in square brackets inside LOD expressions. Missing brackets cause syntax errors.

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