Moving average in Tableau - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the sales trend over time by smoothing out short-term fluctuations using a moving average.

📊 Data: You have monthly sales data for the past year, including the month and total sales amount.

🎯 Deliverable: Create a line chart in Tableau showing monthly sales and a 3-month moving average line to visualize the trend clearly.

Sample Data

Month	Sales
2023-01	1200
2023-02	1350
2023-03	1500
2023-04	1600
2023-05	1700
2023-06	1650
2023-07	1800
2023-08	1900
2023-09	2000
2023-10	2100
2023-11	2200
2023-12	2300

1

Step 1: Connect your sales data to Tableau and load the data source.

No formula needed; just connect the data file or database.

Expected Result

Sales data with Month and Sales columns is available in Tableau.

2

Step 2: Create a line chart showing monthly sales.

Drag 'Month' to Columns shelf and 'Sales' to Rows shelf. Change 'Month' to continuous date if needed.

Expected Result

A line chart showing sales values for each month from January to December 2023.

3

Step 3: Create a calculated field for the 3-month moving average of sales.

Create calculated field named '3-Month Moving Average' with formula: WINDOW_AVG(SUM([Sales]), -2, 0)

Expected Result

Calculated field that computes the average sales of the current month and the previous two months.

4

Step 4: Add the moving average line to the chart.

Drag '3-Month Moving Average' to Rows shelf next to 'Sales'. Change the mark type to Line for both measures.

Expected Result

The chart now shows two lines: the original monthly sales and the smoothed 3-month moving average.

5

Step 5: Format the chart for clarity and add a legend.

Use different colors for sales and moving average lines. Add axis titles and chart title 'Monthly Sales and 3-Month Moving Average'.

Expected Result

A clear, easy-to-read line chart with legend and titles showing sales trends and smoothed trend.

Final Result

Month  | Sales  | 3-Month Moving Average
-----------------------------------------
Jan    | 1200   | 
Feb    | 1350   | 
Mar    | 1500   | 1350
Apr    | 1600   | 1483
May    | 1700   | 1600
Jun    | 1650   | 1650
Jul    | 1800   | 1716
Aug    | 1900   | 1783
Sep    | 2000   | 1900
Oct    | 2100   | 2000
Nov    | 2200   | 2100
Dec    | 2300   | 2200

(Line chart with two lines: monthly sales and 3-month moving average smoothing the trend)

✓The 3-month moving average smooths out short-term ups and downs in sales.

✓Sales show a steady upward trend throughout the year.

✓The moving average line lags slightly behind actual sales but highlights the overall growth clearly.

Bonus Challenge

Create a 6-month moving average line and compare it with the 3-month moving average on the same chart.

Show Hint

Use WINDOW_AVG(SUM([Sales]), -5, 0) for the 6-month moving average calculated field.

Practice

1. What is the main purpose of using a moving average in Tableau visualizations?

easy

A. To smooth out short-term fluctuations and highlight longer-term trends

B. To count the total number of data points in a dataset

C. To filter out all data except the latest value

D. To create a pie chart from time series data

Solution

1. Step 1: Understand moving average concept

   A moving average calculates the average of data points over a specific number of periods to reduce noise.

2. Step 2: Identify its purpose in visualization

   This smoothing helps reveal underlying trends by minimizing short-term ups and downs.

3. Final Answer:

   To smooth out short-term fluctuations and highlight longer-term trends -> Option A

4. Quick Check:

   Moving average = smoothing trends [OK]

Hint: Moving average smooths data to show trends clearly [OK]

Common Mistakes:

• Confusing moving average with total sum
• Thinking it filters data instead of smoothing
• Assuming it creates categorical charts

2. Which of the following is the correct Tableau calculation syntax to compute a 3-period moving average of SUM(Sales)?

easy

A. WINDOW_AVG(SUM([Sales]), 0, 2)

B. WINDOW_AVG(SUM([Sales]), -1, 1)

C. WINDOW_AVG(SUM([Sales]), -2, 0)

D. WINDOW_AVG(SUM([Sales]), -1, 2)

Solution

1. Step 1: Understand WINDOW_AVG parameters

   WINDOW_AVG(expression, start, end) averages values from start to end relative to current row.

2. Step 2: Define 3-period window around current row

   For 3 periods centered on current row, use -1 (previous), 0 (current), and 1 (next), so range is -1 to 1.

3. Final Answer:

   WINDOW_AVG(SUM([Sales]), -1, 1) -> Option B

4. Quick Check:

   3-period window = -1 to 1 [OK]

Hint: Use negative and positive offsets to set window range [OK]

Common Mistakes:

• Using incorrect window range that doesn't cover 3 periods
• Confusing start and end parameters
• Omitting SUM aggregation inside WINDOW_AVG

3. Given the following data points for Sales over 5 days: [100, 120, 140, 160, 180], what is the 3-day moving average value for day 3 using WINDOW_AVG(SUM([Sales]), -1, 1)?

medium

A. 120

B. 160

C. 130

D. 140

Solution

1. Step 1: Identify the 3-day window for day 3

   Day 3 includes day 2 (120), day 3 (140), and day 4 (160) because window is from -1 to +1 relative to day 3.

2. Step 2: Calculate average of these values

   (120 + 140 + 160) / 3 = 420 / 3 = 140

3. Final Answer:

   140 -> Option D

4. Quick Check:

   Average of 120,140,160 = 140 [OK]

Hint: Average values one before, current, and one after [OK]

Common Mistakes:

• Including wrong days in the window
• Calculating sum instead of average
• Using only previous days without current or next

4. You wrote this Tableau calculation for a 5-day moving average: WINDOW_AVG(SUM([Sales]), -2, 2). However, the moving average is not showing correctly on the first two days. What is the likely issue?

medium

A. The window range includes days outside the data, causing NULLs to affect the average

B. SUM aggregation is missing inside WINDOW_AVG

C. WINDOW_AVG requires only positive offsets for the window

D. The calculation should use WINDOW_SUM instead of WINDOW_AVG

Solution

1. Step 1: Analyze window range impact on edge rows

   Window from -2 to 2 means for first two days, some offsets point to non-existent previous days (before data starts).

2. Step 2: Understand effect of NULLs in window

   These NULLs can cause the average to be incorrect or missing because Tableau includes them in calculation.

3. Final Answer:

   The window range includes days outside the data, causing NULLs to affect the average -> Option A

4. Quick Check:

   Edge rows have incomplete windows causing NULL impact [OK]

Hint: Check window range near data edges for NULLs [OK]

Common Mistakes:

• Assuming WINDOW_AVG ignores NULLs automatically
• Confusing aggregation functions inside WINDOW_AVG
• Thinking window offsets must be positive only

5. You want to create a 7-day moving average of daily sales but only for weekdays (Monday to Friday). Which approach correctly handles this in Tableau?

hard

A. Use WINDOW_AVG(SUM([Sales]), -3, 3) and filter out weekends before calculation

B. Use WINDOW_AVG(SUM([Sales]), -6, 0) ignoring weekends in data

C. Create a calculated field that excludes weekends, then use WINDOW_AVG over consecutive weekdays

D. Apply WINDOW_AVG on all days and manually remove weekend values from the result

Solution

1. Step 1: Understand weekday filtering impact

   Simply filtering weekends after calculation or ignoring them breaks the consecutive window needed for moving average.

2. Step 2: Use calculated field to exclude weekends before averaging

   By creating a field that removes weekends, the WINDOW_AVG function works on consecutive weekdays only, producing accurate 7-day averages.

3. Final Answer:

   Create a calculated field that excludes weekends, then use WINDOW_AVG over consecutive weekdays -> Option C

4. Quick Check:

   Exclude weekends before WINDOW_AVG for correct weekday moving average [OK]

Hint: Filter weekends before applying moving average [OK]

Common Mistakes:

• Applying WINDOW_AVG including weekends causing wrong averages
• Using wrong window size ignoring missing days
• Filtering weekends after calculation instead of before