Rolling Window

Average or total a run of rows for every row. Choose where the window sits and when a short window stays blank.

What it does

Rolling Window looks back over a fixed number of rows. It writes one result for each row. A 7-day moving average is the common case.

Before. Daily visits to a website:

After. AVG of visits, window size 3, ordered by date:

The last row averages 140, 120 and 160. The first row has only itself.

Add a moving average

1. Type rolling in the Search transforms box, in the Pipeline panel.
2. Select Rolling Window in the results. The panel opens below the grid.
3. Open Function and pick AVG (moving average), SUM, MIN, MAX, COUNT, MEDIAN or distinct count.
4. Type a number in Window size (rows). The field starts at 7.
5. Open Choose column… and pick the column to aggregate.
6. Open Choose ordering column… and pick your date column. This field is required.
7. Open Partition by (optional) and pick a group column, if you need one.
8. Type a name in Output column name (optional).
9. Click Apply.

The grid updates at once. The step appears in your pipeline, where you can edit or delete it later.

What window size means

The window is a run of rows of the size you give. A size of 7 covers seven rows, and Window position decides which seven.

Where the window sits

• Trailing is the default and the one people mean by a moving average: the current row and the six before it. Nothing from the future gets in, which is what you want for anything you will later forecast from.
• Centred puts the row in the middle: three rows before, the row itself, three after. This is the one that draws the smoothest trend line, because a bump is not dragged forward in time.
• Look ahead takes the current row and the six after it, which answers "what is coming" rather than "where have we been".

Rows at the edges

The first rows have fewer rows above them, so a trailing 7-row average starts as one value, then the mean of two, then three. Those numbers look like the rest of the column and are not comparable with it.

Minimum values fixes that. Set it to 7 and a row whose window holds fewer than seven values comes back blank, so the chart starts where the average becomes real. Leave it empty and the short windows are averaged as they are, which is the old behaviour.

A minimum larger than the window size is refused: every row would be blank, so it is a mistake rather than a setting.

Tips

• Order by (required) has no default. Pick the date or time column for a series.
• The step counts rows, not days. A missing date makes the window cover a longer period.
• Use Partition by (optional) for one series for each store or product. The window then restarts in each group.
• Without a partition, the values of one group flow into the next group.
• Leave Output column name (optional) empty. The app then names the column avg_visits_rolling_7 or similar.
• Fill gaps in your dates first. A missing row cannot enter the window.
• Set Minimum values to the window size when the result is going on a chart. The edge values are the ones readers misread.
• Use Centred for smoothing something you are looking at, and Trailing for anything a decision follows from.

For SQL users

The step runs as one OVER clause with a row frame:

SELECT *, AVG("visits") OVER (ORDER BY "date" ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "avg_visits_rolling_7" FROM data

A centred window moves the frame around the row, and a minimum is a COUNT over the same frame:

ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING          -- centred, size 7
ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING         -- look ahead, size 7

SELECT *, CASE WHEN COUNT("visits") OVER w >= 7 THEN AVG("visits") OVER w END AS "avg_visits_rolling_7"
FROM data WINDOW w AS (ORDER BY "date" ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Related Operations

• Pivot - Pivot table (rows to columns)
• Window Function - Rank, row number, lead, lag
• Smallest - Get nth smallest value as a column