xmetricRecipes

Recipe Tableau and SQL

How do I calculate a 7-day or 28-day moving average?

In Tableau, a trailing 7-day average is WINDOW_AVG(SUM([passengers]), -6, 0): today and the six days before. In SQL it is AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), on one row per calendar day. Pick a multiple of seven: on this ferry data a 30-day average moves about as much from day to day as a 7-day one, and twice as much as a 28-day one.

Updated

The data

Harbour Line ferry, daily passengers in 2025

Synthetic datapassengers.csv

A commuter ferry across the harbour: busy weekdays, quiet weekends, a summer peak and a January ice storm that stopped every crossing for four days, which left no rows at all.

travel_datepassengersrain_mm
2025-01-0111300
2025-01-0210930
2025-01-0310390
2025-01-045840

Step 1

Plot the daily passengers

Every weekend drops to about half a weekday. The summer peak is in there, but the weekly teeth are louder than the trend.

Tableau

SUM([passengers])

SQL

SELECT travel_date, SUM(passengers) AS passengers
FROM input
GROUP BY travel_date
ORDER BY travel_date

Rows

Travel datePassengers
Jan 1, 20251,130
Jan 2, 20251,093
Jan 3, 20251,039
Jan 4, 2025584
Jan 5, 2025464
Jan 6, 20251,086
Jan 7, 20251,177
Jan 8, 20251,113
Jan 9, 20251,212
Jan 10, 20251,149
Jan 11, 2025588
Jan 12, 2025440
Jan 13, 2025964
Jan 14, 20251,174
Jan 15, 20251,189
Jan 16, 20251,058
Jan 17, 20251,048
Jan 18, 2025581
Jan 19, 2025464
Jan 20, 2025594
Jan 25, 2025295
Jan 26, 2025274
Jan 27, 20251,096
Jan 28, 20251,078
Jan 29, 20251,155
Jan 30, 20251,266
Jan 31, 20251,096
Feb 1, 2025583
Feb 2, 2025471
Feb 3, 20251,161
Feb 4, 20251,373
Feb 5, 20251,205
Feb 6, 20251,337
Feb 7, 20251,114
Feb 8, 2025631
Feb 9, 2025496
Feb 10, 20251,281
Feb 11, 20251,273
Feb 12, 20251,325
Feb 13, 20251,283
Feb 14, 20251,283
Feb 15, 2025691
Feb 16, 2025535
Feb 17, 20251,359
Feb 18, 20251,448
Feb 19, 20251,419
Feb 20, 20251,493
Feb 21, 2025883
Feb 22, 2025787
Feb 23, 2025620

First 50 of 361 rows.

335

Step 2

Average the last few rows

Each point is the average of the current row and the ones before it. In Tableau, put travel_date on Columns as an exact date and compute along it (Compute Using: Table (across) or travel_date). This version counts rows, not days, which matters in January.

Tableau

WINDOW_AVG(SUM([passengers]), -(7 - 1), 0)

SQL

WITH d AS (
  SELECT travel_date, SUM(passengers) AS passengers
  FROM input
  GROUP BY travel_date
)
SELECT
  travel_date,
  passengers,
  AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 7 - 1 PRECEDING AND CURRENT ROW) AS avg_passengers
FROM d
ORDER BY travel_date

Rows

Travel datePassengersAvg passengers
Jan 1, 20251,1301,130
Jan 2, 20251,0931,112
Jan 3, 20251,0391,087
Jan 4, 2025584962
Jan 5, 2025464862
Jan 6, 20251,086899
Jan 7, 20251,177939
Jan 8, 20251,113937
Jan 9, 20251,212954
Jan 10, 20251,149969
Jan 11, 202558897
Jan 12, 2025440966
Jan 13, 2025964949
Jan 14, 20251,174949
Jan 15, 20251,189959
Jan 16, 20251,058937
Jan 17, 20251,048923
Jan 18, 2025581922
Jan 19, 2025464925
Jan 20, 2025594873
Jan 25, 2025295747
Jan 26, 2025274616
Jan 27, 20251,096622
Jan 28, 20251,078626
Jan 29, 20251,155708
Jan 30, 20251,266823
Jan 31, 20251,096894
Feb 1, 2025583935
Feb 2, 2025471964
Feb 3, 20251,161973
Feb 4, 20251,3731,015
Feb 5, 20251,2051,022
Feb 6, 20251,3371,032
Feb 7, 20251,1141,035
Feb 8, 20256311,042
Feb 9, 20254961,045
Feb 10, 20251,2811,062
Feb 11, 20251,2731,048
Feb 12, 20251,3251,065
Feb 13, 20251,2831,058
Feb 14, 20251,2831,082
Feb 15, 20256911,09
Feb 16, 20255351,096
Feb 17, 20251,3591,107
Feb 18, 20251,4481,132
Feb 19, 20251,4191,145
Feb 20, 20251,4931,175
Feb 21, 20258831,118
Feb 22, 20257871,132
Feb 23, 20256201,144

First 50 of 361 rows.

Why does the January line look wrong?

Because the average counts rows, not days. The ice storm stopped the ferry for four days and left no rows, so right after it the window reaches back past the storm to busy days in mid-January. Give every day a row, with zero where zero is true, and the dip shows up where it happened.

Common mistake

Averaging rows when days are missing

The ferry stopped from 21 to 24 January and those days have no rows. Counted by rows, the 7-day average on Monday 27 January reaches back to the 17th and says 622 passengers a day. Over the real last seven days it was 238. Fill in a row for every day first.

WrongStarts with 2025-01-27…
Point at a bar to read it.
622Avg passengers

Tableau

WINDOW_AVG(SUM([passengers]), -6, 0)
// missing dates hidden

SQL

SELECT travel_date, avg_passengers
FROM (
  SELECT
    travel_date,
    ROUND(AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS avg_passengers
  FROM input
)
WHERE travel_date = DATE '2025-01-27'
RightStarts with 2025-01-27…
Point at a bar to read it.
238Avg passengers

Tableau

WINDOW_AVG(ZN(SUM([passengers])), -6, 0)
// travel_date pill: Show Missing Values

SQL

WITH days AS (
  SELECT CAST (RANGE AS DATE) AS travel_date
  FROM RANGE (DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 DAY)
), d AS (
  SELECT days.travel_date, COALESCE(i.passengers, 0) AS passengers
  FROM days LEFT JOIN input i ON i.travel_date = days.travel_date
)
SELECT travel_date, avg_passengers
FROM (
  SELECT
    travel_date,
    ROUND(AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS avg_passengers
  FROM d
)
WHERE travel_date = DATE '2025-01-27'

Step 3

Give every calendar day a row

The storm days now exist, with zero passengers, so the window covers real calendar days. The average also stays blank until a full window exists, instead of averaging fewer days at the start. Use zero only when zero is the truth; if the days are missing because data wasn't recorded, leave them empty.

Tableau

IF INDEX() >= 7 THEN WINDOW_AVG(ZN(SUM([passengers])), -(7 - 1), 0)
END
// travel_date pill: Show Missing Values

SQL

WITH days AS (
  SELECT CAST (RANGE AS DATE) AS travel_date
  FROM RANGE (DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 DAY)
), d AS (
  SELECT
    days.travel_date,
    COALESCE(SUM(i.passengers), 0) AS passengers
  FROM days LEFT JOIN input i ON i.travel_date = days.travel_date
  GROUP BY days.travel_date
)
SELECT
  travel_date,
  passengers,
  CASE
    WHEN ROW_NUMBER() OVER (ORDER BY travel_date) >= 7 THEN AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 7 - 1 PRECEDING AND CURRENT ROW)
    END AS avg_passengers
FROM d
ORDER BY travel_date

Rows

Travel datePassengersAvg passengers
Jan 1, 20251,130–
Jan 2, 20251,093–
Jan 3, 20251,039–
Jan 4, 2025584–
Jan 5, 2025464–
Jan 6, 20251,086–
Jan 7, 20251,177939
Jan 8, 20251,113937
Jan 9, 20251,212954
Jan 10, 20251,149969
Jan 11, 202558897
Jan 12, 2025440966
Jan 13, 2025964949
Jan 14, 20251,174949
Jan 15, 20251,189959
Jan 16, 20251,058937
Jan 17, 20251,048923
Jan 18, 2025581922
Jan 19, 2025464925
Jan 20, 2025594873
Jan 21, 20250705
Jan 22, 20250535
Jan 23, 20250384
Jan 24, 20250234
Jan 25, 2025295193
Jan 26, 2025274166
Jan 27, 20251,096238
Jan 28, 20251,078392
Jan 29, 20251,155557
Jan 30, 20251,266738
Jan 31, 20251,096894
Feb 1, 2025583935
Feb 2, 2025471964
Feb 3, 20251,161973
Feb 4, 20251,3731,015
Feb 5, 20251,2051,022
Feb 6, 20251,3371,032
Feb 7, 20251,1141,035
Feb 8, 20256311,042
Feb 9, 20254961,045
Feb 10, 20251,2811,062
Feb 11, 20251,2731,048
Feb 12, 20251,3251,065
Feb 13, 20251,2831,058
Feb 14, 20251,2831,082
Feb 15, 20256911,09
Feb 16, 20255351,096
Feb 17, 20251,3591,107
Feb 18, 20251,4481,132
Feb 19, 20251,4191,145

First 50 of 365 rows.

Why 7 and 28, not 30?

Because commuting follows the week. Any window that holds whole weeks has each weekday the same number of times, so as it slides, a Monday drops out and a Monday comes in. Any other size swaps a weekday for a weekend day and back, and the line wobbles for no reason.

Step 4

Which window sizes jitter?

For each window size, how far the average moves from one day to the next, on average over the year. Tableau shows one window size at a time; the SQL tries every size from 3 to 35. Bigger windows should move less. They do, except for a sawtooth: every multiple of seven (7, 14, 21, 28, 35) drops to a low, and the sizes just after it jump back up.

Tableau

WINDOW_AVG(ABS([Moving average] - LOOKUP([Moving average], -1)))
// [Moving average] is the previous step saved as a field

SQL

WITH days AS (
  SELECT CAST (RANGE AS DATE) AS travel_date
  FROM RANGE (DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 DAY)
), d AS (
  SELECT
    days.travel_date,
    COALESCE(SUM(i.passengers), 0) AS passengers
  FROM days LEFT JOIN input i ON i.travel_date = days.travel_date
  GROUP BY days.travel_date
), w AS (
  SELECT RANGE AS window_days
  FROM RANGE (3, 36)
), a AS (
  SELECT
    w.window_days,
    d.travel_date,
    AVG(d2.passengers) AS avg_passengers
  FROM w CROSS JOIN d JOIN d d2 ON d2.travel_date BETWEEN d.travel_date - (w.window_days - 1) * INTERVAL 1 DAY AND d.travel_date
  WHERE d.travel_date >= DATE '2025-01-01' + (w.window_days - 1) * INTERVAL 1 DAY
  GROUP BY w.window_days, d.travel_date
), c AS (
  SELECT
    window_days,
    ABS(avg_passengers - LAG(avg_passengers) OVER (PARTITION BY window_days ORDER BY travel_date)) AS change
  FROM a
)
SELECT window_days, AVG(change) AS daily_wobble
FROM c
GROUP BY window_days
ORDER BY window_days

Rows

Window daysChange
3327
4246
5188
6101
738.84
876.86
9107
10101
1192.96
1281.49
1349.76
1424.46
1544.95
1663.86
1762.26
1859.4
1954.33
2035.17
2120.2
2233.35
2346
2445.85
2544.47
2641.41
2728.5
2817.71
2927.5
3037.13
3136.89
3236.55
3334.41
3424.82
3517.01

At the current setting, a 7-day average moves 39 passengers a day. Try 28, then 30: two more days in the window, and the line moves about twice as much.

Common mistake

A 30-day window for a weekly business

Thirty days is four weeks and two extra days. As the window slides, a busy weekday drops out while a quiet weekend day comes in, or the other way round, and the line wobbles with nothing behind it. Over the year a 30-day average moves 37 passengers a day; a 28-day average moves 18.

Wrong
Point at a bar to read it.
37.1Daily wobble

Tableau

WINDOW_AVG(ZN(SUM([passengers])), -29, 0)

SQL

WITH days AS (
  SELECT CAST (RANGE AS DATE) AS travel_date
  FROM RANGE (DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 DAY)
), d AS (
  SELECT days.travel_date, COALESCE(i.passengers, 0) AS passengers
  FROM days LEFT JOIN input i ON i.travel_date = days.travel_date
), a AS (
  SELECT
    travel_date,
    AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_passengers,
    ROW_NUMBER() OVER (ORDER BY travel_date) AS k
  FROM d
)
SELECT ROUND(AVG(ABS(avg_passengers - prev)), 1) AS daily_wobble
FROM (
  SELECT
    avg_passengers,
    LAG(avg_passengers) OVER (ORDER BY travel_date) AS prev,
    k
  FROM a
)
WHERE k > 30
Right
Point at a bar to read it.
17.7Daily wobble

Tableau

WINDOW_AVG(ZN(SUM([passengers])), -27, 0)

SQL

WITH days AS (
  SELECT CAST (RANGE AS DATE) AS travel_date
  FROM RANGE (DATE '2025-01-01', DATE '2026-01-01', INTERVAL 1 DAY)
), d AS (
  SELECT days.travel_date, COALESCE(i.passengers, 0) AS passengers
  FROM days LEFT JOIN input i ON i.travel_date = days.travel_date
), a AS (
  SELECT
    travel_date,
    AVG(passengers) OVER (ORDER BY travel_date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW) AS avg_passengers,
    ROW_NUMBER() OVER (ORDER BY travel_date) AS k
  FROM d
)
SELECT ROUND(AVG(ABS(avg_passengers - prev)), 1) AS daily_wobble
FROM (
  SELECT
    avg_passengers,
    LAG(avg_passengers) OVER (ORDER BY travel_date) AS prev,
    k
  FROM a
)
WHERE k > 28

Common mistake

Off by one: -7 averages eight days

Offset 0 is today. WINDOW_AVG(..., -7, 0) covers today and seven days back: eight rows, and the window no longer holds each weekday once.

Wrong
Point at a bar to read it.
8N

Tableau

WINDOW_AVG(SUM([passengers]), -7, 0)

SQL

SELECT MAX(n) AS days_in_window
FROM (
  SELECT
    COUNT(*) OVER (ORDER BY travel_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS n
  FROM input
)
Right
Point at a bar to read it.
7N

Tableau

WINDOW_AVG(SUM([passengers]), -6, 0)

SQL

SELECT MAX(n) AS days_in_window
FROM (
  SELECT
    COUNT(*) OVER (ORDER BY travel_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS n
  FROM input
)

Run it on your own data

Any table with a date and a measure works: orders, sign-ups, sessions, tickets. Drop your CSV and pick the two columns; the steps above recompute in your browser, and nothing is uploaded. If your file has several rows per day, the steps total them per day first.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, passengers.csv, shows the shape.

Questions

Why -6 and not -7?

Offset 0 is the current day, so -6 to 0 is seven days. -7 to 0 is eight. The same goes for SQL: 6 PRECEDING AND CURRENT ROW.

Should I use a centered average?

For looking back at history, a centered window (-3 to 3) lines the average up with the days it summarises; a trailing average runs about half a window behind. On a live dashboard a centered window leaves the last days incomplete, so keep trailing windows there.

What does RANGE do differently from ROWS in SQL?

RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW looks at calendar dates, so a missing day isn't replaced by an older one. It still averages only the days that have rows; a closed day doesn't count as zero unless you add it.

How do I average a rate, such as conversion?

Don't average the daily rates. Divide the window's total of the numerator by the window's total of the denominator: WINDOW_SUM(SUM([orders]), -6, 0) / WINDOW_SUM(SUM([visits]), -6, 0).

Is my data uploaded?

No. Your CSV is read and calculated in this browser tab.

Sources