over
over, aggregator option
over defines an inclusive range relative to the left-side table of an
aggregation.
Examples
table Orders = with
[| as Product, as OrderDate, as Quantity |]
[| "A", date(2020, 1, 1), 1 |]
[| "A", date(2020, 1, 2), 2 |]
[| "A", date(2020, 1, 3), 3 |]
Orders.Mavg = sum(Orders.Quantity)
by Orders.Product
over Orders.OrderDate = [-1 .. 0]
Orders.Cumul = sum(Orders.Quantity)
by Orders.Product
over Orders.OrderDate = [.. 0]
show table "Over" with
Orders.OrderDate
Orders.Mavg
Orders.Cumul
This produces the following table:
| OrderDate | Mavg | Cumul |
|---|---|---|
| 2020-01-01 | 1 | 1 |
| 2020-01-02 | 3 | 3 |
| 2020-01-03 | 5 | 6 |
Remarks
For calendar tables, integer bounds are interpreted as day/week/month offsets.