LOD with date dimensions in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Fixed Sales per Month

You have a sales dataset with daily transactions. You want to calculate total sales fixed at the month level, ignoring the day detail.

Which Tableau LOD expression correctly calculates total sales fixed at the month level?

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

B { INCLUDE DATETRUNC('month', [Order Date]) : SUM([Sales]) }

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

D SUM({ FIXED [Order Date] : [Sales] })

💡 Hint

Think about which LOD keyword fixes the aggregation at the month level, ignoring finer date details.

The FIXED LOD expression fixes the aggregation at the specified level, here the month. INCLUDE and EXCLUDE adjust the level of detail but do not fix it strictly. Option C is invalid syntax.

❓ visualization

intermediate

Best Visualization for Monthly Sales Trend with LOD

You created a FIXED LOD calculation for monthly sales ignoring day details. Which visualization best shows the monthly sales trend over a year?

A Line chart with month on X-axis and FIXED monthly sales on Y-axis

B Pie chart showing sales distribution by month

C Bar chart with day on X-axis and daily sales on Y-axis

D Scatter plot with sales vs. order date

💡 Hint

Consider which chart type best shows trends over time.

A line chart with month on the X-axis and monthly sales on the Y-axis clearly shows trends over time. Pie charts and scatter plots are less effective for trends. Bar chart by day shows too much detail.

❓ data_modeling

advanced

Handling Fiscal Year in LOD Calculations

Your company uses a fiscal year starting in April. You want to calculate total sales fixed by fiscal year using LOD expressions. Which approach correctly handles the fiscal year in Tableau?

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

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

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

D { FIXED YEAR(DATEADD('month', -3, [Order Date])) : SUM([Sales]) }

💡 Hint

Think about shifting dates to align with fiscal year starting in April.

Option D shifts the date by -3 months so that January becomes October of previous fiscal year, effectively aligning with fiscal year starting in April. Tableau does not have a built-in 'fiscalyear' date part, so option D is invalid. Options A and C use calendar year.

🔧 Formula Fix

advanced

Identify Error in LOD Expression with Date Dimension

Which option contains a syntax error in the Tableau LOD expression?

Expression options:

A { FIXED [Order Date] : SUM([Sales]) }

B { FIXED DATETRUNC('month' [Order Date]) : SUM([Sales]) }

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

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

💡 Hint

Look carefully at the syntax of the DATETRUNC function in each option.

Option B is missing a comma between 'month' and [Order Date] inside DATETRUNC, causing a syntax error. The other options have correct syntax.

🧠 Conceptual

expert

Impact of LOD Expressions on Performance with Large Date Dimensions

You have a large dataset with millions of rows and a detailed date dimension. You use FIXED LOD expressions on date truncated to day level. What is the most likely impact on performance and why?

A Performance may degrade because FIXED LOD calculates aggregations before filtering, increasing data scanned

B Performance improves because FIXED LOD caches results and reduces query time

C No impact on performance because LOD expressions are optimized automatically

D Performance improves because FIXED LOD reduces the number of rows by truncating dates

💡 Hint

Consider how FIXED LOD expressions interact with data filtering and query execution.

FIXED LOD expressions compute aggregations before dimension filters are applied, which can increase the amount of data processed and slow performance, especially on large datasets. Options B and D misunderstand how FIXED LOD works. Option A is incorrect because optimization depends on context.

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