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]

Orders.Previous = sum(Orders.Quantity)
  by Orders.Product
  over Orders.OrderDate = [.. -1]

show table "Over" with
  Orders.OrderDate
  Orders.Mavg
  Orders.Cumul
  Orders.Previous

This produces the following table:

Cumul includes the current date. Previous includes all dates through the preceding day. Unlike [-1 .. 0], [.. -1] has no lower bound.

Remarks

For calendar tables, integer bounds are interpreted as day/week/month offsets.

With no lower bound, the upper bound must be a constant integer at most zero.