Shape · Summarize and reshape

Running Aggregations

Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.

Also known as: Cumulative sum, running total

How it works

Group by is optional and restarts the series per group. Order by is required: it is the sequence the totals build up along. Click Add operation for each running column: a function (Sum, Average, Min, Max, Count, Median or Std dev), a column (Count offers All rows) and an output name, which defaults to a name such as Running SUM of amount. Every row carries the value accumulated up to and including itself; the input columns are kept.

Inputs and outputs

Settings

{
  operations: [{ func: "SUM"|"AVG"|"MIN"|"MAX"|"COUNT"|"MEDIAN"|"STDDEV", column: string, outputColumn?: string }]
  orderBy: [{ column: string, direction: "asc"|"desc" }]
  groupBy?: string[]
}

Example

You need year-to-date sales per region. Order by date ascending, group by region, and add Sum of amount as ytd.

More in Summarize and reshape

• Aggregate — Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.
• Group Aggregate — Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.
• Pivot — Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.
• Unpivot — Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Rows — Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.