I85PREP
⌘ K
Tableau Data Analyst study materials
Tableau Data Analyst study resources

The Misleading Moving Average: Choosing the Right Calculation

Tableau Data Analyst · Put your knowledge to work in context.

All case studies

The Misleading Moving Average: Choosing the Right Calculation

A marketing analyst is analyzing weekly sales data and wants to show a smoothed trend to highlight underlying patterns. They create a moving average calculation in Tableau using a table calculation: WINDOW_AVG(SUM([Sales]), -3, 0). They apply it to a line chart with Week on the Columns shelf. The chart shows a 4-week moving average. However, when they present the chart, a colleague points out that the moving average line starts at week 4 and the first three weeks have no value, which makes the chart look like sales started at week 4. Additionally, the analyst notices that the moving average line is much smoother than the raw data, but it seems to lag behind the actual peaks. The analyst wants to show both the raw weekly sales and the moving average on the same chart, with a clear indication of the smoothing effect.

1. What are the issues with the current moving average calculation, and how can the analyst improve the visualization to avoid misinterpretation?

Read the worked answer

Issues:

  1. Missing values at the start: The moving average of the last 4 weeks (including current) cannot be computed for the first 3 weeks because there isn't enough data. This is mathematically correct but can mislead viewers into thinking sales began at week 4. To address this, the analyst can:

    • Use a moving average that includes future weeks as well (e.g., centered moving average) but that also has edge effects.
    • Start the chart at week 4 and annotate that the moving average requires 4 weeks of data.
    • Use a different smoothing method like exponential smoothing that can start from week 1.
    • Show the raw data for all weeks and the moving average only where available, with a clear legend and perhaps a dashed line for the moving average.

  2. Lag: A trailing moving average (using past weeks) inherently lags behind the actual data. This is expected but should be explained. To reduce lag, use a centered moving average (e.g., WINDOW_AVG(SUM([Sales]), -1, 2) for a 4-week centered average) but that requires future data and may not be suitable for real-time. Alternatively, use a weighted moving average that gives more weight to recent weeks.

  3. Visualization: Show both raw and moving average on the same axis with distinct colors and line styles (e.g., raw as thin gray line, moving average as thick blue line). Add a tooltip explaining the calculation. Consider using a dual axis to separate scales if needed.

  4. Clarity: Add a subtitle or annotation explaining that the moving average is a 4-week trailing average and that the first three weeks are not shown for that line.

By addressing these, the analyst can present a clearer, more accurate view.

2. If the analyst wants to use a table calculation that avoids the missing values issue, what alternatives exist in Tableau, and what are their trade-offs?

Read the worked answer

Alternatives to avoid missing values at the start:

  1. Centered moving average: Use WINDOW_AVG(SUM([Sales]), -2, 1) for a 4-week centered average. This uses 2 weeks before and 1 week after. However, it still has missing values at the beginning and end (first 2 weeks and last week). Trade-off: still has edge effects, and cannot be used for the most recent week if future data is unavailable.

  2. Exponential smoothing: Use a calculated field with a recursive formula, but Tableau does not support recursive calculations natively. You can use WINDOW_AVG with a larger window or use a Python/R integration for exponential smoothing. Trade-off: complexity, requires external service.

  3. Use a fixed number of periods with null handling: Modify the calculation to ignore nulls: WINDOW_AVG(SUM([Sales]), -3, 0) but if there are fewer than 4 weeks, it returns null. You can use IFNULL(WINDOW_AVG(SUM([Sales]), -3, 0), SUM([Sales])) to fall back to raw sales for early weeks. Trade-off: the early weeks are not smoothed, which might be acceptable.

  4. Use a different aggregation: Instead of moving average, use a 4-week rolling sum or a trend line. Trade-off: different interpretation.

  5. Use Tableau's built-in trend lines: Add a trend line to the raw data. Trade-off: less control over smoothing.

  6. Use a parameter to control the window size: Allow users to adjust the window, but still has edge effects.

The analyst should choose based on the goal: if the goal is to show underlying trend, a centered moving average might be better; if real-time, a trailing average with a note is acceptable.