Documentation

    Rolling Calc

    Add columns calculated over neighboring rows while keeping every row: running or moving sums, averages, minimums, maximums and counts, the previous or next value and differences or percent changes. Sort the rows first. Partition By restarts the calculation within each group.

    Calculate a running total and moving average

    Manual Input has one value column in the order 10, 20, 30, 40. Rolling Calc starts with one calculation using Running sum and Window / offset 0, producing cumulative totals.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    valuevalue_running_sum
    1010
    2030
    3060
    40100
    1. In the calculation, change Operation to Running avg and Window / offset to 2. The results become 10, 15, 25 and 35: each row averages itself and the row before it, when there is one.
    2. Set Output to moving_average to give the result column a clearer name.
    3. Select Require full window. The first result becomes NULL because only one row is available; the remaining results stay 15, 25 and 35.

    When to use

    Use Rolling Calc for running totals, moving averages and change from the previous row. It keeps every row. To reduce a table to one row per group, use Summarize.

    Read the output

    The initial output keeps value and adds value_running_sum. The results are 10, 30, 60 and 100: each total includes the current value and everything before it. All four input rows stay.

    With Running avg and Window / offset 2, the first row uses only 10. The next averages 10 and 20 to give 15, followed by 25 from 20 and 30, then 35 from 30 and 40.

    Require full window replaces the first partial result with NULL. It doesn't remove the row or require every value in the window to be non-NULL.

    To put rows in the intended order before calculating, use Sort.

    To reduce a group to one summary row, use Summarize.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    Configure the block

    Add calculations with their own operation, source column, window or offset and output name.

    Add

    Under Calculations, add an output column. Move calculations up or down to set output column order, or remove calculations you no longer need. Each calculation reads the incoming columns.

    Add

    Under Partition By, add a column for each grouping key. Each distinct combination gets an independent calculation. Leave empty to calculate across the whole table.

    Operation

    Running sum adds the values in each window. Running avg averages its non-NULL values. Running min, max and count return the minimum, maximum and non-NULL count. Lag and Lead read a value from N rows back or ahead; Difference and Percent change compare with N rows back. Default: Running sum.

    Column

    Choose the numeric column to calculate over. Lag and Lead also accept nonnumeric columns.

    Window / offset

    For running operations, use 0 for all rows from the start through the current row. A positive whole number N includes the current row and up to N minus 1 preceding rows. For Lag, Lead, Difference and Percent change, use an offset of at least 1; Lead looks ahead and the others look back. Default: 0.

    Require full window

    Apply to every fixed rolling window: return NULL until that calculation has enough rows in its partition. Cumulative calculations and offset operations are unaffected. Default: false.

    Output

    Name the new result column. The automatic name combines the source column, operation and positive window or offset. Names must differ from input columns and other calculation outputs.

    Rolling Calc FAQs

    Does Window 7 mean seven days?
    No. It means seven rows, including the current row. Time gaps between observations don't change the window size.
    What happens to missing values?
    Sum and average ignore NULL values. A window containing only NULL values produces NULL. Missing rows still occupy a place in the window and count toward Require full window.
    Does it sort the rows automatically?
    No. It uses the incoming row order. This example is already ordered; for other data, use Sort on the date or sequence column before Rolling Calc.
    How does Partition By work?
    Each group uses only its own rows, in incoming order. A cumulative total restarts for each group, and a fixed window only includes earlier rows from the same group.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.