How much had accumulated by each day? running_total adds a column up as it goes down the rows, so each row carries the total of everything to that point.
run(' diary then sort [on_] then add [so_far] as running_total([x]), [before] as previous([x]), [after] as following([x]) by [g]')
g
on_
x
so_far
before
after
a
2026-01-02
10
10
NA
20
a
2026-01-05
20
30
10
NA
b
2026-01-06
30
30
NA
NA
diary |>sort(on_) |>add(so_far =running_total(x), before =previous(x),after =following(x), by = g)
g
on_
x
so_far
before
after
a
2026-01-02
10
10
NA
20
a
2026-01-05
20
30
10
NA
b
2026-01-06
30
30
NA
NA
(diary>> sort(col.on_)>> add(so_far = running_total(col.x), before = previous(col.x), after = following(col.x), by = col.g))
g
on_
x
so_far
before
after
a
2026-01-02
10
10
<NA>
20
a
2026-01-05
20
30
10
<NA>
b
2026-01-06
30
30
<NA>
<NA>
The first row of a group has nothing before it and the last has nothing after it, so those are missing.
One row is the usual distance and not the only one. Say how far as a second argument, which is how you compare a month against the same month a year earlier:
How far is written out rather than worked out. A distance that changed from row to row would be a different question, and no engine underneath answers it here, so a column in that place is refused. Nought is refused too, because that is the column itself, and a negative is refused with the name of the word that goes the other way.
Error:
!
illegal: `previous` already says which way it goes, so how far is counted forwards from 1. To look the other way, use `following`
|
3 | then add [back] as previous([x], -2)
| ^^
illegal: `previous` already says which way it goes, so how far is counted forwards from 1. To look the other way, use `following`
|
3 | then add [back] as previous([x], -2)
| ^^
rolling asks an aggregate of the last few rows and answers on every row, so a moving average is one word wrapping another. The aggregate goes inside, one of total, average, median, smallest, largest and standard_deviation. The number says how many rows the window holds, the row itself included:
The first row is missing, and that is the answer rather than a gap: one row is not the two-row average, it is a different question wearing its name. A window that is not yet full answers missing, and so does a full window with a missing value inside it. That is how every library with a rolling word answers too. by restarts the window inside each group, exactly as it does for the totals above. The aggregate inside reads a plain column, so a computed value is one add away, in a step of its own.
Every word here needs a sort in front of it, and says so if it does not get one. A total so far means nothing until something has said so far in what order:
Error:
!
illegal: `running_total(...)` reads the rows in the order they are in, and nothing has said what that order is. Sort before it: `then sort [when] then add [so_far] as running_total(...)`. `rank([revenue] descending)` is the one that says what it goes by, so it needs no sort
|
2 | then add [so_far] as running_total([x])
| ^^^^^^^^^^^^^^^^^^
try: collect(diary >> add(so_far = running_total(col.x)))except GodError as refusal:print(refusal)
illegal: `running_total(...)` reads the rows in the order they are in, and nothing has said what that order is. Sort before it: `then sort [when] then add [so_far] as running_total(...)`. `rank([revenue] descending)` is the one that says what it goes by, so it needs no sort
|
2 | then add [so_far] as running_total([x])
| ^^^^^^^^^^^^^^^^^^
rank is the exception, because its argument is the order: rank(x) says what it goes by, so it needs no sort in front of it. The words this chapter added never get that shortcut. A total so far, the value before, the value after, an aggregate of the last few rows: every one begins with sort saying in what order the rows are read.