Year-over-year comparison in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Year-over-Year Sales Growth

You have a sales dataset with a Sales Amount and a Date field. Using Tableau's calculated fields, which formula correctly calculates the year-over-year sales growth percentage?

A (SUM([Sales Amount]) - LOOKUP(SUM([Sales Amount]), -12)) / LOOKUP(SUM([Sales Amount]), -12)

B (SUM([Sales Amount]) - ZN(SUM([Sales Amount]))) / ZN(SUM([Sales Amount]))

C ([Sales Amount] - LOOKUP(SUM([Sales Amount]), -1)) / LOOKUP(SUM([Sales Amount]), -1)

D (SUM([Sales Amount]) - PREVIOUS_VALUE(SUM([Sales Amount]))) / PREVIOUS_VALUE(SUM([Sales Amount]))

💡 Hint

Think about how many periods back you need to look to compare the same month last year.

Option A correctly uses LOOKUP with an offset of -12 to compare the current month's sales to the same month in the previous year, assuming monthly data.

❓ visualization

intermediate

Best Visualization for Year-over-Year Sales Comparison

You want to show year-over-year sales trends for multiple years in a clear and easy-to-understand way. Which type of Tableau visualization is best suited for this?

A Line chart with separate lines for each year showing monthly sales

B Stacked bar chart showing total sales per year

C Pie chart comparing total sales of two years

D Scatter plot showing sales vs. profit for each year

💡 Hint

Think about how to show trends over time and compare multiple years clearly.

A line chart with separate lines for each year allows easy comparison of monthly sales trends across years, making year-over-year analysis intuitive.

🧠 Conceptual

advanced

Understanding Year-over-Year Calculation Challenges

What is a common challenge when calculating year-over-year metrics in Tableau for datasets with missing months or irregular dates?

A Tableau cannot perform year-over-year calculations on irregular date data.

B Year-over-year calculations do not require date fields, so missing months do not affect results.

C Tableau automatically fills missing months with zeros, so no challenge exists.

D LOOKUP functions may compare to incorrect periods if data is missing, causing inaccurate year-over-year results.

💡 Hint

Consider how missing data affects relative period calculations.

When months are missing, LOOKUP with fixed offsets may compare to wrong rows, leading to incorrect year-over-year values. Data must be complete or handled carefully.

❓ data_modeling

advanced

Modeling Data for Year-over-Year Analysis

Which data model design best supports efficient year-over-year analysis in Tableau?

A A table with only sales records for months with sales, no date gaps

B A flat table with sales and date columns including all dates, even if sales are zero

C Separate tables for each year with no date relationships

D A table with aggregated yearly sales only, no monthly detail

💡 Hint

Think about how missing dates affect time comparisons.

A flat table with all dates ensures continuous time series, allowing accurate year-over-year calculations and visualizations without gaps.

🔧 Formula Fix

expert

Debugging Incorrect Year-over-Year Calculation

A Tableau user created this calculated field for year-over-year sales growth:
(SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)
But the results are incorrect when viewing monthly data. What is the most likely cause?

A The calculation needs to use WINDOW_SUM instead of SUM.

B SUM aggregation is incorrect; should use AVG instead.

C LOOKUP offset of -1 compares to previous month, not previous year, causing wrong comparison.

D Division by zero error occurs because LOOKUP returns null for first month.

💡 Hint

Consider what the offset number in LOOKUP means in a monthly time series.

Using LOOKUP with offset -1 compares the current month to the previous month, not the same month last year. For year-over-year, offset should be -12 for monthly data.

Practice

1. What is the main purpose of a year-over-year comparison in Tableau?

easy

A. To compare values from one year to the previous year

B. To calculate the total sales for a single year

C. To display data only for the current year

D. To filter data by month

Solution

1. Step 1: Understand year-over-year comparison

   Year-over-year comparison is used to see how a value changes from one year to the next.

2. Step 2: Identify the correct purpose

   Comparing values from one year to the previous year matches the definition of year-over-year comparison.

3. Final Answer:

   To compare values from one year to the previous year -> Option A

4. Quick Check:

   Year-over-year = compare year to previous year [OK]

Hint: Year-over-year means comparing this year to last year [OK]

Common Mistakes:

• Confusing year-over-year with total yearly sales
• Thinking it filters data instead of comparing years
• Assuming it only shows current year data

2. Which Tableau function is commonly used to get the previous year's value for year-over-year calculations?

easy

A. SUM()

B. WINDOW_SUM()

C. DATEPART()

D. LOOKUP()

Solution

1. Step 1: Identify function for previous value

   LOOKUP() function in Tableau returns a value from a previous or next row, useful for previous year values.

2. Step 2: Confirm correct function

   WINDOW_SUM() sums over a window, DATEPART() extracts date parts, SUM() totals values, but only LOOKUP() fetches previous year value directly.

3. Final Answer:

   LOOKUP() -> Option D

4. Quick Check:

   Previous year value = LOOKUP() [OK]

Hint: LOOKUP() fetches previous row values, perfect for last year [OK]

Common Mistakes:

• Using SUM() instead of LOOKUP() for previous year
• Confusing DATEPART() with fetching previous values
• Using WINDOW_SUM() which sums but doesn't get previous year

3. Given this Tableau calculation for year-over-year growth:
SUM([Sales]) - LOOKUP(SUM([Sales]), -1)
What does this calculation return?

medium

A. The percentage growth of sales year-over-year

B. The difference in sales between the current year and the previous year

C. The sales for the previous year only

D. The total sales for the current year

Solution

1. Step 1: Analyze the calculation components

   SUM([Sales]) gives current year sales; LOOKUP(SUM([Sales]), -1) fetches previous year sales.

2. Step 2: Understand the subtraction

   Subtracting previous year sales from current year sales gives the difference in sales year-over-year.

3. Final Answer:

   The difference in sales between the current year and the previous year -> Option B

4. Quick Check:

   Current year sales - previous year sales = difference [OK]

Hint: Subtract LOOKUP() from current sum for difference [OK]

Common Mistakes:

• Thinking it calculates percentage growth
• Assuming it returns only previous year sales
• Confusing it with total sales calculation

4. You created a year-over-year calculation using LOOKUP but the results are incorrect. What is the most likely cause?

medium

A. The calculation uses SUM instead of AVG

B. The data source is missing the Sales field

C. The table calculation direction is not set to compute using Year

D. The filter is applied after the calculation

Solution

1. Step 1: Identify common error in LOOKUP calculations

   LOOKUP depends on table calculation direction; if not set to Year, it fetches wrong rows.

2. Step 2: Confirm why direction matters

   Setting compute using Year ensures LOOKUP moves along years, giving correct previous year values.

3. Final Answer:

   The table calculation direction is not set to compute using Year -> Option C

4. Quick Check:

   Wrong direction = wrong previous year value [OK]

Hint: Always set table calc direction to Year for LOOKUP [OK]

Common Mistakes:

• Ignoring table calculation direction
• Changing aggregation from SUM to AVG unnecessarily
• Assuming missing fields cause LOOKUP errors

5. You want to create a dashboard showing year-over-year sales growth percentage. Which calculation correctly computes this in Tableau?

 (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1) * 100 

hard

A. This calculation will cause a division by zero error if previous year sales are zero

B. This calculation should use WINDOW_SUM instead of SUM

C. This calculation correctly computes the year-over-year growth percentage

D. This calculation needs to add 1 before multiplying by 100

Solution

1. Step 1: Understand the calculation formula

   The formula calculates difference divided by previous year sales, then multiplies by 100 for percentage.

2. Step 2: Identify potential issue

   If previous year sales are zero, division by zero occurs causing error or infinite result.

3. Step 3: Evaluate other options

   WINDOW_SUM is not needed here; adding 1 is incorrect for percentage growth calculation.

4. Final Answer:

   This calculation will cause a division by zero error if previous year sales are zero -> Option A

5. Quick Check:

   Division by zero risk if previous year sales = 0 [OK]

Hint: Check for zero in denominator to avoid errors [OK]

Common Mistakes:

• Ignoring division by zero possibility
• Replacing SUM with WINDOW_SUM unnecessarily
• Adding 1 incorrectly in percentage formula