Lesson 14 of 15 · Lab
Running totals and rolling averages
A running total adds each row to everything before it. A rolling average, also called a moving average, averages only the last few rows, which smooths out single spikes. Use a running total for "how much so far", and a rolling average for "how fast lately".
| Value | Running total | Rolling average, 2 rows |
|---|---|---|
| 10 | 10 | 10 |
| 20 | 30 | 15 |
| 30 | 60 | 25 |
| 40 | 100 | 35 |
Don Tremendous at fictional Vandelay Coffee Supply says South led all quarter. South ends ahead by €322, and a total can't test the rest. Filter drops the unpriced invoice and Sort orders each region by date, because a running total needs both. Meet Don and his report.
South ends the quarter ahead. Did it lead the whole way?
No. A lead of €322 can easily be recent, and the running total shows exactly when it changed.
Read the running total, then narrow the window
- Rolling Calc, selected in this live flow in Strata, Oplema's browser app for data prep, holds a running sum of
net_eur, restarted for eachregionby Partition By. Window / offset is 0, which means every row so far. - Read
runningin the preview. North's six rows come first and climb to 5,578. Scroll for South's. - Look at the chart under this flow. It draws the running sum over time. Find where the lines cross.
- Select Rolling Calc and set Window / offset to 2, then select the block.
- Read
running. Each row now adds only itself and the row before, so it shows recent pace instead of the score. The chart stays put, a saved picture of step 2.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).
Where the lines should cross
South passes North on September 23, on Initech's second invoice, and finishes at 5,900 to North's 5,578. With a window of 2, the last rows are North 1,710 and South 3,000.
A total hides when it happened
So you can tell Max Thrust, the founder, that South leads, but only since September 23, and only because of one new customer. Don's "all quarter" isn't in any version of the data, even his own.
To get an average in this flow, change Operation to Running avg and keep the window. See the Rolling Calc reference.
Common rolling window mistakes
- Partial windows. The first row of a 2-row window has only one value, so its average is a partial one, 10 in the table above. Turn on Require full window to get NULL instead of a thin early figure.
- Row windows on calendar questions. Seven rows are seven days only if no day is missing. Fill the gaps, or use a calendar window.
- No sort. The total follows the order the rows arrived in, and it still looks plausible.
- No partition. Without Partition By, North and South add into one line.
Window functions in SQL, and ranges in Excel
SELECT region, day,
SUM(net) OVER (PARTITION BY region ORDER BY day) AS running_total,
AVG(net) OVER (PARTITION BY region ORDER BY day
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM dailyROWS counts rows. For a calendar window, PostgreSQL and DuckDB accept RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW. In Excel, =SUM($B$2:B2) filled down is a running total, because the first cell stays fixed. =AVERAGE(B2:B3) filled down is a rolling average, because both ends move.