create_pivot
Build a pivot table from a source block: choose row and column fields, aggregate a measure with SUM, COUNT, AVERAGE, etc., and get an addressable result block for further calculations.
Instructions
Cross-tabulate a table: one row per distinct value of rows, one column per distinct value of columns, each cell aggregating measure.
The result is an ordinary block, so every cell is addressable: BLOCKREF("<name>", "<a rows value>", "<a columns value>"). That is what lets you put one number from it in a sentence or feed it to another calculation.
You declare the recipe; the engine generates every cell from it. Do not write formulas, and do not write into the block — renaming a field of the source rebuilds the pivot rather than breaking it, which is only true because nothing is hand-written.
rows and columns must be fields whose values REPEAT (a region, a quarter, a status). Pointing either at an id gives one row per record, which is the source table again, not a pivot.
IMPORTANT: a pivot's numbers are live but its SHAPE is not. When new values appear in the source, its rows and columns fall behind while every number in it stays correct — call build__refresh_pivot. describe_block reports when that has happened.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| func | No | How to aggregate. Defaults to SUM. COUNT counts matching RECORDS and ignores `measure`; COUNTA counts the records whose `measure` is filled in, which is how you ask how complete a column is. | |
| name | No | Ref name for the new block. Defaults to "<source>_pivot". | |
| rows | Yes | Field of the source whose distinct values become the ROWS. Its values must repeat. | |
| order | No | Order of the rows. `ascending` (default) sorts; `firstSeen` keeps the source's own sequence; `custom` uses `order_values`. | |
| source | Yes | Ref name of the table to pivot. | |
| columns | No | Field whose distinct values become the COLUMNS. Omit for a simple group-by with one value column. | |
| filters | No | Which source records count at all. Omit to count every one. Applied to the rows AND the numbers, so a group left with no records gets no row rather than a row reading 0. | |
| measure | Yes | Field being aggregated. Normally a number field. | |
| row_total | No | Name for a column totalling each row across EVERY value of `columns` — e.g. "Total". Omit for no total column. | |
| order_values | No | The row sequence for `order: custom`. A value you omit is placed after the listed ones, never hidden. | |
| extra_measures | No | Extra columns with their own function and measure, for showing more than one number per group. |