Lesson 4 of 8 · Lab
Fill missing dates and calculate a 7-day average
Sequence creates rows that don't exist yet, such as one per day. Rolling Calc averages over rows, so the days must all be there before a 7-row window means 7 days.
Marit wants the trend per shop. Old Town closes on Mondays, so its Mondays have no row in the daily table, and a closed day is easy to miss.
This live flow in Strata, Oplema's browser app for data prep, starts from two tables at fictional Kramerica Bikes: the daily table, 86 rows in a Manual Input, a table typed into the flow, and Marit's fleet table. Union passes the days to Formula, which turns an empty bikes_out into 0, and Sort orders them by shop and day. The lower branch waits for a list of days, and the Formula at the bottom right rounds avg_7 to two places.
A 7-row average on Old Town's September 30: which days does it cover?
September 23 to 30 without Monday, September 28, when it was closed. Seven rows, eight days.
Average, then fill the gaps
Put Rolling Calc between Sort and the rounding Formula, and read Old Town's 7-row average on September 30: 13.57. Then give every shop a row for every September day, so the same window covers 7 days.
Check: Union has 90 rows and still 1,717 bikes out, and Old Town's September 30 reads 11.86. Sequence makes the days; the Lookup and Join already find the missing ones.
If you're stuck
- Sort is selected. In the palette, select Transform and drag Rolling Calc into the gap below it. Drag from Sort's right dot to its left dot, and from its right dot to the Formula below it. If the Formula turns red, its wire comes from Sort rather than Rolling Calc.
- Select Rolling Calc. Set Operation to Running avg, Column to
bikes_outand Window / offset to 7. In Output, select all (Ctrl+A or Cmd+A) and typeavg_7. Under Partition By, addshop. - Select the Formula at the bottom right and scroll its preview to Old Town's last row, September 30:
avg_7is 13.57. - From IO, drag Sequence to the left of Lookup. Set Start to
2026-09-01and Stop to2026-09-30. In Column Name, select all (Ctrl+A or Cmd+A) and typehire_on, then drag from its right dot to Lookup's upper left dot. - Select Lookup: 90 rows, every day for every shop. Select Join and open OUTPUT LEFT: the 4 Mondays Old Town was closed.
- Drag from Join's top right dot, its Left output, to Union's left dot. Select the Formula at the bottom right: Old Town's September 30 now reads 11.86.
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).
What you should see
The four new days add 0 bikes out, so the total holds. Harbour and Station Road don't change: they opened every day.
Rows are not days
Rolling Calc counts rows, not days. 13.57 is seven open days spread over eight calendar days. 11.86 counts the closed Monday as 0 bikes out. Marit asks how busy each week was, so a closed day belongs in the average as 0.
Join's Left output doubles as a gap list: before you fill anything, it shows exactly which days were missing. Here they're all Old Town's Mondays, so nothing unexpected is hiding.
Your line for Marit: "Old Town's last seven days averaged 11.86 bikes out a day, counting the Monday it was closed."
See the Sequence and Rolling Calc references. For later: Running totals and rolling averages covers choosing a window.
Common rolling average mistakes
- Leaving out Partition By. Harbour's last days then leak into the next shop's first average.
- Averaging unsorted rows. Rolling Calc takes rows in the order they arrive. Sort by shop and day first.
Date spines in SQL and Excel
SELECT s.shop, d.day, coalesce(b.bikes_out, 0) AS bikes_out,
round(avg(coalesce(b.bikes_out, 0)) OVER (PARTITION BY s.shop ORDER BY d.day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS avg_7
FROM generate_series(DATE '2026-09-01', DATE '2026-09-30', INTERVAL 1 DAY) d(day)
CROSS JOIN fleet s
LEFT JOIN daily b ON b.shop = s.shop AND b.hire_on = d.day;generate_series is SQL's Sequence and CROSS JOIN is Lookup's Every Row. In Excel, =AVERAGEIFS over a date range averages the days that exist; to count closed days as 0, list every date first.