Moving average in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate 3-Period Moving Average Using WINDOW_AVG

You have a sales dataset with daily sales. You want to calculate a 3-day moving average of sales using Tableau's WINDOW_AVG function. Which of the following calculated fields will correctly compute the 3-day moving average for each day?

A WINDOW_AVG(SUM([Sales]), -1, 1)

B WINDOW_AVG(SUM([Sales]), -2, 0)

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

D WINDOW_AVG(SUM([Sales]), -3, 0)

💡 Hint

Think about how the window offsets define the range of days included in the average.

The WINDOW_AVG function calculates the average of an expression over a range of data points. Using offsets -2 to 0 includes the current day and the two previous days, making a 3-day moving average.

❓ visualization

intermediate

Identify Correct Moving Average Line Chart

You created a line chart showing daily sales and added a 7-day moving average line. Which visualization best follows best practices for showing moving averages?

A Bar chart with daily sales and moving average combined in one bar without legend or axis labels.

B Scatter plot with daily sales points and moving average points overlapping without connecting lines.

C Pie chart showing proportion of sales per day with moving average as a separate pie slice.

D Line chart with daily sales as thin blue line and 7-day moving average as thick red line with legend and clear axis labels.

💡 Hint

Moving averages are best shown as smooth lines to show trends over time.

Option D uses a line chart with clear distinction between daily sales and moving average lines, proper legend, and axis labels, which is best practice for trend visualization.

🧠 Conceptual

advanced

Understanding Moving Average Window Size Impact

How does increasing the window size of a moving average affect the trend line in a time series visualization?

A It smooths the trend more, reducing noise but may delay detection of changes.

B It makes the trend line more sensitive to daily fluctuations.

C It causes the trend line to become jagged and less smooth.

D It removes all seasonality and cyclic patterns completely.

💡 Hint

Think about how averaging over more points affects responsiveness.

Larger window sizes average more data points, smoothing short-term fluctuations but causing lag in reflecting recent changes.

🔧 Formula Fix

advanced

Identify Error in Moving Average Calculation

You wrote this Tableau calculated field for a 5-day moving average: WINDOW_AVG(SUM([Sales]), 0, 4). What issue will this cause in the visualization?

Tableau

WINDOW_AVG(SUM([Sales]), 0, 4)

A It calculates a 5-day moving average correctly including past and future days.

B Syntax error due to incorrect WINDOW_AVG parameters.

C The moving average only looks forward, ignoring previous days, causing lag in trend detection.

D It causes division by zero error when there are fewer than 5 days.

💡 Hint

Check the window offsets to see which days are included.

The offsets 0 to 4 include the current day and the next 4 days, so it looks forward only, which can cause lag and misleading trends.

🎯 Scenario

expert

Designing a Dashboard with Multiple Moving Averages

You need to build a sales dashboard showing daily sales with 7-day and 30-day moving averages. Which approach best ensures clarity and usability for non-technical users?

A Use a single line chart with daily sales as a thin line, 7-day moving average as a medium line, and 30-day moving average as a thick line, with a clear legend and color coding.

B Create three separate charts for daily sales, 7-day, and 30-day moving averages without legends or labels.

C Use a stacked bar chart combining daily sales and moving averages in one bar per day.

D Show only the 30-day moving average line to reduce clutter and omit daily sales.

💡 Hint

Think about how to compare multiple trends clearly in one view.

Option A uses a single chart with distinct line styles and colors plus a legend, making it easy for users to compare daily sales and moving averages.

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