Year-over-year comparison in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to see how sales have changed year-over-year to understand growth trends.

📊 Data: You have monthly sales data for the years 2022 and 2023. The data includes Date, Sales Amount, and Region.

🎯 Deliverable: Create a Tableau dashboard that shows monthly sales for 2022 and 2023 side by side, with a calculated field showing the percentage change year-over-year.

Sample Data

Date	Sales Amount	Region
2022-01-01	10000	North
2022-02-01	12000	North
2022-03-01	11000	North
2023-01-01	13000	North
2023-02-01	12500	North
2023-03-01	14000	North
2022-01-01	8000	South
2022-02-01	8500	South
2022-03-01	9000	South
2023-01-01	9500	South
2023-02-01	10000	South
2023-03-01	10500	South

1

Step 1: Connect your Tableau workbook to the sales data source containing Date, Sales Amount, and Region.

No formula needed.

Expected Result

Data is loaded and visible in Tableau's Data pane.

2

Step 2: Create a calculated field named 'Year' to extract the year from the Date field.

YEAR([Date])

Expected Result

The 'Year' field shows 2022 or 2023 for each record.

3

Step 3: Create a calculated field named 'Month' to extract the month name from the Date field.

DATENAME('month', [Date])

Expected Result

The 'Month' field shows month names like 'January', 'February', etc.

4

Step 4: Build a view with 'Month' on Columns and 'SUM(Sales Amount)' on Rows.

Drag 'Month' to Columns shelf, drag 'Sales Amount' to Rows shelf and set aggregation to SUM.

Expected Result

A bar chart or line chart showing total sales per month.

5

Step 5: Add 'Year' to the Color shelf to separate sales by year.

Drag 'Year' to Color on Marks card.

Expected Result

Sales for 2022 and 2023 appear side by side with different colors.

6

Step 6: Create a calculated field named 'YoY Change %' to calculate the percentage change in sales compared to the previous year for each month.

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

Expected Result

The 'YoY Change %' field calculates the month-over-month year change as a decimal.

7

Step 7: Add 'YoY Change %' as a label on the chart for the 2023 data points.

Drag 'YoY Change %' to Label on Marks card and filter to show only for Year = 2023.

Expected Result

Percentage change labels appear above 2023 sales bars or points.

8

Step 8: Format the 'YoY Change %' labels to show as percentages with one decimal place.

Right-click 'YoY Change %' field, select Format, set number format to Percentage with 1 decimal.

Expected Result

Labels show values like 18.2%, -4.0%, etc.

9

Step 9: Create a dashboard combining the monthly sales chart with YoY change labels and add a title 'Year-over-Year Sales Comparison'.

Use Tableau Dashboard pane to add the worksheet and a text box for the title.

Expected Result

Dashboard shows clear monthly sales comparison with YoY percentage changes.

Final Result

Year-over-Year Sales Comparison

Month      | 2022 Sales | 2023 Sales | YoY Change %
---------------------------------------------------
January    | 18000      | 22500      | +25.0%
February   | 20500      | 22500      | +9.8%
March      | 20000      | 24500      | +22.5%

(Line chart with two colored lines for 2022 and 2023 sales, labels showing YoY % above 2023 points)

✓Sales increased in all three months from 2022 to 2023.

✓January saw the highest growth at 25%.

✓February growth was modest at about 10%.

✓March also showed strong growth of over 22%.

Bonus Challenge

Add a filter to the dashboard to allow users to select a specific Region and see the year-over-year comparison for that region only.

Show Hint

Use a Region filter on the dashboard and apply it to the sales worksheet.

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