LOD with date dimensions in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the total sales per month and also the average daily sales within each month for the past year.

📊 Data: You have sales transaction data with columns: Order Date, Sales Amount, and Order ID.

🎯 Deliverable: Create a Tableau dashboard showing monthly total sales and average daily sales per month using LOD expressions with date dimensions.

Sample Data

Order Date	Order ID	Sales Amount
2023-01-05	1001	150
2023-01-05	1002	200
2023-01-15	1003	100
2023-02-10	1004	300
2023-02-15	1005	250
2023-02-15	1006	100
2023-03-01	1007	400
2023-03-20	1008	350
2023-03-20	1009	150
2023-03-25	1010	200

1

Step 1: Create a calculated field to extract the month and year from Order Date for grouping.

Create calculated field 'Month Year' with formula: DATETRUNC('month', [Order Date])

Expected Result

A new field showing the first day of each month for each order date, e.g., 2023-01-01, 2023-02-01.

2

Step 2: Create a calculated field for total sales per month using LOD expression.

{FIXED [Month Year] : SUM([Sales Amount])}

Expected Result

Total sales aggregated for each month, e.g., January total sales = 450.

3

Step 3: Create a calculated field to count distinct days with sales in each month using LOD expression.

{FIXED [Month Year] : COUNTD([Order Date])}

Expected Result

Number of unique sales days per month, e.g., January has 2 unique sales days.

4

Step 4: Create a calculated field for average daily sales per month by dividing total sales by distinct sales days.

[Total Sales per Month] / [Distinct Sales Days per Month]

Expected Result

Average daily sales for each month, e.g., January average daily sales = 225.

5

Step 5: Build a dashboard with a bar chart showing 'Month Year' on X-axis and 'Total Sales per Month' on Y-axis.

Use 'Month Year' as Columns, 'Total Sales per Month' as Rows in a bar chart.

Expected Result

Bar chart showing total sales for each month.

6

Step 6: Add a line chart on the same dashboard showing 'Month Year' on X-axis and 'Average Daily Sales per Month' on Y-axis.

Use 'Month Year' as Columns, 'Average Daily Sales per Month' as Rows in a line chart.

Expected Result

Line chart showing average daily sales trend per month.

7

Step 7: Combine both charts in a dashboard with clear titles and legends for easy comparison.

Arrange bar and line charts vertically with titles: 'Monthly Total Sales' and 'Average Daily Sales per Month'.

Expected Result

Dashboard clearly showing monthly total sales and average daily sales side by side.

Final Result

Monthly Sales Dashboard

Month Year | Total Sales (Bar)  | Average Daily Sales (Line)
------------------------------------------------------------
2023-01    | ██████████ 450     | ────────225
2023-02    | ██████████████ 650 | ──────────217
2023-03    | ██████████████████ 1100 | ──────────275

(Bar length proportional to total sales, line shows average daily sales trend)

✓March had the highest total sales with 1100.

✓Average daily sales ranged from 217 to 275 per day.

✓January had 2 sales days, February had 3, and March had 4.

Bonus Challenge

Create a calculated field to show the percentage contribution of each month's total sales to the yearly total sales using LOD.

Show Hint

Use FIXED LOD to calculate yearly total sales: {FIXED : SUM([Sales Amount])} and then divide monthly total sales by this value.

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