LOD with date dimensions in Tableau - Step-by-Step Guide

Introduction

This feature helps you calculate values for specific dates or date parts, even when your view shows different levels of detail. It solves the problem of getting accurate totals or averages for dates regardless of how you slice your data.

When you want to find total sales per month even if your view shows daily sales.

When you need to compare yearly averages but your dashboard shows data by week.

When you want to fix a calculation at the quarter level while exploring daily data.

When you want to count unique customers per year regardless of the current view.

When you want to show the first purchase date per customer even if the view is by product.

Steps

Step 1: Open

- Tableau Desktop and connect to your data source

Your data fields appear in the Data pane

Step 2: Right-click

- in the Data pane on an empty space and select Create > Calculated Field

A calculation editor window opens

Step 3: Type

- the LOD expression using date dimensions, for example: { FIXED YEAR([Order Date]) : SUM([Sales]) }

The calculation is ready to fix sales by year regardless of the view

Step 4: Click

- OK to save the calculated field

The new field appears in the Data pane

Step 5: Drag

- the new calculated field onto Rows or Columns shelf or into the view

The view updates showing sales fixed by the year of the order date

Step 6: Change

- the date dimension in the view (e.g., from day to month)

The LOD calculation remains fixed at the year level, not changing with the view

Before vs After

Before

View shows daily sales totals, and totals change when filtering by month or quarter

After

View shows sales totals fixed by year, totals stay the same even when filtering by month or quarter

Settings Reference

Calculation Editor

📍 Right-click Data pane > Create > Calculated Field

To write and save LOD expressions using date dimensions

Default: Empty

Date Part Functions

📍 Calculation Editor toolbar or type manually

To extract parts of a date for use in LOD calculations

Default: None

Aggregation Functions

📍 Calculation Editor

To aggregate data inside the LOD expression

Default: None

Common Mistakes

Using a date field directly inside LOD without extracting a date part

The calculation may fix at the exact date level, causing unexpected results when the view changes

Use date functions like YEAR() or MONTH() inside the LOD to fix the calculation at the desired date level

Not using FIXED keyword and expecting the calculation to ignore view filters

Other LOD types like INCLUDE or EXCLUDE depend on the view and may not fix the date level as intended

Use FIXED LOD expressions to lock calculations at specific date dimensions regardless of the view

Summary

LOD expressions let you fix calculations at specific date levels like year or month.

Use date functions inside LOD to control the date granularity.

FIXED LOD keeps calculations stable even when the view changes date levels.

Practice

1. What does a FIXED LOD expression with DATETRUNC('month', [Order Date]) do in Tableau?

easy

A. Calculates a value fixed at the month level, ignoring view filters on date.

B. Calculates a value that changes with every day in the month.

C. Filters data to only show the first day of each month.

D. Aggregates data by year instead of month.

Solution

1. Step 1: Understand FIXED LOD with DATETRUNC

   FIXED LOD fixes calculation at the specified date granularity, here month.

2. Step 2: Effect on calculation

   It ignores filters on date and calculates once per month, not per day.

3. Final Answer:

   Calculates a value fixed at the month level, ignoring view filters on date. -> Option A

4. Quick Check:

   LOD with DATETRUNC('month') fixes by month [OK]

Hint: FIXED + DATETRUNC fixes calculation at chosen date level [OK]

Common Mistakes:

• Thinking it filters data instead of fixing calculation
• Confusing month with day granularity
• Assuming it aggregates by year

2. Which of the following is the correct syntax for a FIXED LOD expression to calculate total sales by quarter in Tableau?

easy

A. {FIXED [DATETRUNC('quarter', [Order Date])] : SUM([Sales])}

B. {FIXED [Order Date], DATETRUNC('quarter') : SUM([Sales])}

C. {FIXED DATETRUNC('quarter', [Order Date]) : SUM(Sales)}

D. {FIXED DATETRUNC('quarter', [Order Date]) : SUM([Sales])}

Solution

1. Step 1: Check FIXED LOD syntax

   Correct syntax is {FIXED [dimension] : aggregation} where dimension can be an expression like DATETRUNC.

2. Step 2: Validate each option

   {FIXED DATETRUNC('quarter', [Order Date]) : SUM([Sales])} correctly uses {FIXED DATETRUNC('quarter', [Order Date]) : SUM([Sales])} with proper brackets and field names.

3. Final Answer:

   {FIXED DATETRUNC('quarter', [Order Date]) : SUM([Sales])} -> Option D

4. Quick Check:

   Correct FIXED LOD syntax uses brackets and field names [OK]

Hint: Use curly braces and brackets correctly in FIXED LOD [OK]

Common Mistakes:

• Missing brackets around field names
• Incorrect placement of DATETRUNC
• Using aggregation inside DATETRUNC

3. Given the FIXED LOD expression {FIXED DATETRUNC('year', [Order Date]) : SUM([Sales])}, what will be the result if the view shows data by month for 2023 with monthly sales as [1000, 1500, 1200]?

medium

A. [1000, 1500, 1200]

B. [3700, 3700, 3700]

C. [1233, 1233, 1233]

D. [0, 0, 0]

Solution

1. Step 1: Calculate yearly total sales

   Sum monthly sales: 1000 + 1500 + 1200 = 3700 for the year 2023.

2. Step 2: Apply FIXED LOD at year level

   LOD fixes total sales at year level, so each month shows 3700 regardless of monthly values.

3. Final Answer:

   [3700, 3700, 3700] -> Option B

4. Quick Check:

   Year-level FIXED LOD repeats yearly total per month [OK]

Hint: LOD FIXED at year repeats yearly total for all months [OK]

Common Mistakes:

• Assuming monthly sums instead of yearly total
• Adding averages instead of sums
• Confusing FIXED with INCLUDE or EXCLUDE

4. You wrote this LOD expression: {FIXED [DATETRUNC('month', [Order Date])] : SUM([Sales])} but Tableau shows an error. What is the likely problem?

medium

A. DATETRUNC cannot be used inside FIXED LOD expressions.

B. DATETRUNC should be wrapped in square brackets as a field.

C. Unnecessary square brackets around the DATETRUNC expression.

D. DATETRUNC expression must be inside parentheses, not brackets.

Solution

1. Step 1: Check syntax for FIXED LOD with expressions

   When using an expression like DATETRUNC inside FIXED, it must NOT be enclosed in square brackets to be treated as a dimension (brackets are only for field names).

2. Step 2: Identify the bracket issue

   The expression has unnecessary square brackets around DATETRUNC('month', [Order Date]), causing syntax error.

3. Final Answer:

   Unnecessary square brackets around the DATETRUNC expression. -> Option C

4. Quick Check:

   Expressions in FIXED must not be bracketed [OK]

Hint: Bracket expressions inside FIXED LOD properly [OK]

Common Mistakes:

• Putting brackets around DATETRUNC inside FIXED
• Confusing parentheses and brackets
• Assuming DATETRUNC is a field, not a function

5. You want to compare monthly sales to the total sales of the year for each month in Tableau. Which LOD expression correctly calculates the yearly total sales fixed by year to use in this comparison?

hard

A. {FIXED DATETRUNC('year', [Order Date]) : SUM([Sales])}

B. {FIXED [YEAR([Order Date])] : SUM([Sales])}

C. {INCLUDE DATETRUNC('year', [Order Date]) : SUM([Sales])}

D. {EXCLUDE DATETRUNC('month', [Order Date]) : SUM([Sales])}

Solution

1. Step 1: Identify correct date truncation for year

   DATETRUNC('year', [Order Date]) returns the first day of the year, suitable for fixing yearly level.

2. Step 2: Use FIXED LOD for consistent yearly total

   FIXED LOD with DATETRUNC('year', [Order Date]) fixes sales at year level, ignoring view filters.

3. Step 3: Compare monthly sales to yearly total

   This expression allows comparing each month's sales to the fixed yearly total.

4. Final Answer:

   {FIXED DATETRUNC('year', [Order Date]) : SUM([Sales])} -> Option A

5. Quick Check:

   FIXED + DATETRUNC('year') fixes yearly total for comparison [OK]

Hint: Use FIXED with DATETRUNC('year') for yearly totals [OK]

Common Mistakes:

• Using YEAR() instead of DATETRUNC for FIXED
• Using INCLUDE or EXCLUDE instead of FIXED
• Excluding month instead of fixing year