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.