span
span, keyword
The span keyword unfilters calendar tables over a date range.
Example
table Orders = with
[| as OrderDate, as Quantity |]
[| date(2021, 1, 2), 1 |]
[| date(2021, 1, 3), 2 |]
[| date(2021, 1, 5), 2 |]
[| date(2021, 1, 5), 1 |]
[| date(2021, 1, 9), 2 |]
[| date(2021, 1, 12), 2 |]
[| date(2021, 1, 17), 2 |]
[| date(2021, 1, 17), 1 |]
span date = [min(Orders.OrderDate) .. max(Orders.OrderDate)]
expect Orders.date = Orders.OrderDate
Day.Quantity = sum(Orders.Quantity)
show table "Daily orders" with
date
Day.Quantity
This outputs the following table:
| Date | Quantity |
|---|---|
| 2021-01-02 | 1 |
| 2021-01-03 | 2 |
| 2021-01-04 | 0 |
| 2021-01-05 | 3 |
| 2021-01-06 | 0 |
| 2021-01-07 | 0 |
| 2021-01-08 | 0 |
| 2021-01-09 | 2 |
| 2021-01-10 | 0 |
| 2021-01-11 | 0 |
| 2021-01-12 | 2 |
| 2021-01-13 | 0 |
| 2021-01-14 | 0 |
| 2021-01-15 | 0 |
| 2021-01-16 | 0 |
| 2021-01-17 | 3 |
Remarks
span introduces a block; the unfiltering ends when the block ends. Use
keep span to avoid extra indentation.
When a span on date is introduced, the Day, Week, and Month tables are
created with date, week, and month as their primary dimensions.