Formula Multi-Row
Compute a column from other rows with SQL window functions, such as running totals, ranking, lag/lead and moving averages.
What it does
A regular Formula sees one row at a time. Multi-Row Formula reads the whole table in order, so it can build a column from OTHER rows (running totals, row-to-row change, moving averages, ranking) using SQL window functions. Switch "Each row reads" to the previous row's result and each row builds on the answer above it instead: a balance that never goes below zero, a loan balance, a capped total.
Inputs and outputs
| Inputs | One. |
| Outputs | One. |
When to use it
- Running / cumulative totals
- Compare a row to the previous or next
- Moving averages
- Rank rows within a group
- A running balance with a floor or a cap, or a loan balance
Examples
Running total
SUM([amount]) OVER (ORDER BY _row)
Order every window by _row (incoming order) or your own column.
From previous row
[amount] - LAG([amount]) OVER (ORDER BY _row)
Per-group total
SUM([amount]) OVER (PARTITION BY [acct] ORDER BY _row)
Row number
ROW_NUMBER() OVER (ORDER BY _row)
Stock never below zero
GREATEST(0, COALESCE(_prev, 0) + [received] - [shipped])
Each row reads the previous row's result. A plain running total would carry a shortfall forward; this one stops at zero.
Loan balance
COALESCE(_prev, [principal]) * (1 + [rate]) - [payment]
The rate can change from row to row.
Points capped at 1,000
LEAST(1000, COALESCE(_prev, 0) + [earned] - [spent])
Smoothing
0.3 * [sales] + 0.7 * COALESCE(_prev, [sales])
The first row seeds itself.
Tips
- _row is the implicit incoming-order column; use it in ORDER BY to keep results stable.
- PARTITION BY restarts the calculation per group.
- _prev is the previous row's result, and it is empty on the first row, so wrap it in COALESCE. Pick group columns and it starts empty again for each group.
- When each row reads the previous row's result, rows are taken in the order they arrive. Put a Sort first.
- Window functions are not available when each row reads the previous row's result. Compute them in a Multi-Row Formula placed before this one.
- A mistake in an expression is caught in the builder, before you run.