Aggregated Custom Expression Functions
Summary
- Aggregated Custom Expression Functions work across many rows
- A Basic function folds the rows into a single value with
initialValue+reducer - An Advanced function also post-processes that value, and can return a different value on each row
- They are available in Calculated Column Aggregated and Alert Aggregated Expressions
Developers can add Custom Aggregation Expression Functions to AdapTableQL.
These functions operate on a set of rows — typically on one Column.
Hint
- It also possible to optionally split the aggregation by using
GROUP_BY - And you can narrow the aggregation function by using
WHERE
Custom Aggregation Functions are registered in expressionOptions.customAggregatedFunctions.
AdapTableQL then lists them in the Aggregated Expression Editor for Calculated Columns and Alerts.
Why write Custom Aggregations?
- Your own maths, first-class: VWAP, running totals, Kth largest, custom risk measures, used like
SUMorAVG - Declare capability, get feature:
cumulative: truegives running value per row;weight:truegivesWEIGHT([col]) - Grouping and filtering for free: every Custom Aggregation accepts
GROUP_BYandWHERE - Available to Users: listed in Expression Editor with your description, signatures and examples
- One small object: an
initialValueand areducer- with optional hooks only when you need them
Note
AG Grid's custom aggFuncs only fill group and total rows; a Custom Aggregation gives a value on every row
- This demo defines 2 Custom Aggregated Functions (explained in Declared Capabilities below):
RUNNING_SUM- declarescumulative: trueVWAP- declaresweight: trueandcumulative: true
- They are used in 4 Aggregated Calculated Columns:
Running Notional-RUNNING_SUM{[notional], CUMULATIVE([tradeDate])}Desk Running Notional-RUNNING_SUM{[notional], GROUP_BY([desk]), CUMULATIVE([tradeDate])}Ticker VWAP-VWAP{[price], WEIGHT([quantity]), GROUP_BY([ticker])}Running Ticker VWAP-VWAP{[price], WEIGHT([quantity]), GROUP_BY([ticker]), CUMULATIVE([tradeDate])}
- Sort by
Trade Date(the default) and seeRunning Notionalbuild up trade by trade - Change the
Quantityof anAAPLtrade and watch both VWAP Columns move for everyAAPLtrade - Open the Expression Editor for a Calculated Column to see
RUNNING_SUMandVWAPlisted with their clauses
Note
- The first argument in an aggregation function is typically a Column, e.g.
SUMSQ{[rating]} - However, extra arguments are also allowed, e.g.
LARGE{[amount], 2}
There are 2 levels of Custom Aggregated Function:
| Level | Properties | Returns |
|---|---|---|
| Basic | initialValue, reducer | One value for the set (or for each group) |
| Advanced | Basic, plus processAggregatedValue and / or prepareRowValue | One value for the set, or a value per row |
Hint
Start with a Basic function; only reach for the Advanced hooks when the reducer result is not the final answer
Basic Aggregations
A Basic Aggregation Function needs just 2 properties:
initialValue— the starting value of the accumulatorreducer— called once per row, returning the new accumulator
The value the reducer returns for the last row is the result of the function.
Developer Guide
Defining a Basic Custom Aggregated Function
This example creates 2 Basic functions:
SUMSQ— the sum of squares of a numeric Column (like Excel'sSUMSQ)COUNT_ABOVE— how many values in the Column are greater than a threshold passed as a second argument
expressionOptions: {
customAggregatedFunctions: {
SUMSQ: {
initialValue: 0,
reducer: (context) => {
const value = context.currentValue;
if (value == null || Number.isNaN(Number(value))) {
return context.accumulator;
}
return context.accumulator + value * value;
},
inputTypes: ['number'],
description: 'Mimics SUMSQ in Excel - the sum of squares of the values',
signatures: ['SUMSQ{[numericColumn]}'],
examples: ['SUMSQ{[rating]}', 'SUMSQ{[rating], GROUP_BY([currency])}'],
},
COUNT_ABOVE: {
initialValue: 0,
reducer: (context) => {
const threshold = context.args[0];
return context.currentValue > threshold
? context.accumulator + 1
: context.accumulator;
},
inputTypes: ['number', 'number'],
description: 'Counts the values greater than the given threshold',
signatures: ['COUNT_ABOVE{[numericColumn], threshold: number}'],
examples: ['COUNT_ABOVE{[amount], 1000}'],
},
},
}Add each function to expressionOptions.customAggregatedFunctions.
This is a Record (or a callback that returns one):
- keys: custom function names (CAPITAL LETTERS by convention)
- values: of type
AggregatedExpressionFunction
The starting value for the accumulator.
Here its 0 in both cases (a running total and a running count).
Hint
If the reducer mutates its accumulator, supply a factory instead — e.g. initialValue:()=>[] — so each aggregation (and group) starts fresh
Called for each row in the set; returns the new accumulator.
The AggregatedExpressionReducerContext includes:
accumulator— previous return value, orinitialValueon the first callcurrentValue— the aggregated Column's value in this rowindex— position in the Column dataargs— any extra args, e.g. threshold inCOUNT_ABOVE{[amount], 1000}aggColumnId,getValueForColId,rowNode,groupByColumnIds
Shown in the Expression Editor:
inputTypes— optional argument types (helps editor cue suitable Columns)description— what the Function doessignatures— how it should be calledexamples— small Expressions that use itcategory— Functions-menu section; omit for the Aggregation section
Advanced Aggregations
Sometimes the reducer result is not the final answer and more is required.
For example the function needs sorting, or a Kth value picked out, or a different result shown on each row.
Advanced Aggregation functions can include 2 optional hooks, which run in this order:
filterRow (optional) → reducer per row → processAggregatedValue once → prepareRowValue per row
| Hook | Runs | Use it to | Example |
|---|---|---|---|
processAggregatedValue | Once, after every row has been reduced | Turn the reducer result into the final value (sort, pick Kth, build a lookup) | LARGE |
prepareRowValue | Per row, after processAggregatedValue | Return a different value on each row (rank, percentage, "in the top K") | RANK |
Note
- This is a very common pattern when using a Custom Aggregation Function:
- Collect values in the
reducer, i.e. do the expensive work (e.g. sorting) once inprocessAggregatedValue - Then build a
Map/SetsoprepareRowValueis a cheap lookup per row
Developer Guide
Defining Advanced Custom Aggregated Functions
This example creates 2 functions:
LARGE— the Kth largest value (like Excel'sLARGE); usesprocessAggregatedValueRANK— the rank of each row's value (1 = smallest); usesprocessAggregatedValueandprepareRowValue
expressionOptions: {
customAggregatedFunctions: {
LARGE: {
initialValue: () => [],
reducer: (context) => {
if (context.currentValue != undefined) {
context.accumulator.push(context.currentValue);
}
return context.accumulator;
},
processAggregatedValue: (context) => {
const k = context.args[0];
const values = context.aggregatedValue;
if (k == undefined || k <= 0 || k > values.length) {
return undefined;
}
return [...values].sort((a, b) => b - a)[k - 1];
},
description: 'Mimics LARGE in Excel - the Kth largest value',
signatures: ['LARGE{[numericColumn], K: number}'],
examples: ['LARGE{[amount], 2}'],
},
RANK: {
initialValue: () => [],
reducer: (context) => {
if (context.currentValue != undefined) {
context.accumulator.push(context.currentValue);
}
return context.accumulator;
},
processAggregatedValue: (context) => {
const sorted = [...context.aggregatedValue].sort((a, b) => a - b);
const rankMap = new Map();
sorted.forEach((value, index) => rankMap.set(value, index + 1));
return rankMap;
},
prepareRowValue: (context) => {
const rowValue = context.getValueForColId(context.aggColumnId);
return context.aggregatedValue.get(rowValue);
},
description: 'The rank of each row value in the list (1 = smallest)',
signatures: ['RANK{[numericColumn]}'],
examples: ['RANK{[amount]}'],
},
},
}Both functions just collect the values; the real work happens afterwards.
Note the factory initialValue: () => [], because the reducer mutates the array.
processAggregatedValue runs once, after every row has been reduced. Whatever it returns becomes the aggregated value.
LARGEsorts once and returns the Kth value (Karrives incontext.args)RANKsorts once and returns aMapof value → rank
AggregatedExpressionProcessAggValueContext includes aggregatedValue (the reducer result), rowNodes (the rows in this set / group) and args.
prepareRowValue runs for each row, after processAggregatedValue. Use it when the result differs per row.
RANK reads the row's own value and looks up its rank in the Map.
AggregatedExpressionPrepareRowValueContext includes the current rowNode, aggregatedValue, aggColumnId and getValueForColId.
filterRow runs before the reducer; return false to leave a row out of the aggregation.
It receives AggregatedExpressionFilterRowContext.
Hint
prepareRowValuecan also return a boolean - see the demo'sTOPfunction- it returns
truewhen a row's value is among the K largest, for use in a boolean expression
Declared Capabilities
Every custom aggregation accepts GROUP_BY and WHERE.
The remaining clauses are capabilities the definition declares — validation and the Expression Editor both pick them up automatically, exactly as they do for shipped aggregations like SUM.
Capabilities apply to both levels: below, RUNNING_SUM is a Basic function and VWAP an Advanced one.
| Property | Enables | Notes |
|---|---|---|
cumulative: true | CUMULATIVE([orderCol]) — a running value per row | The rows are walked in orderCol order (restarting per GROUP_BY partition) and each row receives the accumulator so far. processAggregatedValue and prepareRowValue apply to that running value. |
cumulative: { currentValue } | Same, with a custom per-row projection | Use when the running value is not simply the accumulator (e.g. a running average kept as { sum, count }). currentValue receives the accumulator so far plus rowNode, index and weightValue. |
weight: true | WEIGHT([weightCol]) | The weight Column's value for the current row arrives as weightValue in the reducer, prepareRowValue, filterRow and cumulative.currentValue contexts. |
returnType | Static result type | 'number', 'text', 'date', 'boolean' or 'input' (same type as the aggregated Column). Drives the Calculated Column data type and static type checks; without it a Calculated Column defaults to number. |
Caution
- Using
CUMULATIVEorWEIGHTon a custom function that does not declare the capability is a validation error - So the definition is the single source of truth for what an Expression may say
customAggregatedFunctions: {
RUNNING_SUM: {
cumulative: true,
initialValue: 0,
reducer: (context) => context.accumulator + (context.currentValue ?? 0),
},
VWAP: {
// Σ(price · weight) / Σ(weight)
initialValue: () => ({ weighted: 0, weights: 0 }),
reducer: (context) => {
const weight = context.weightValue ?? 1;
context.accumulator.weighted += context.currentValue * weight;
context.accumulator.weights += weight;
return context.accumulator;
},
weight: true,
processAggregatedValue: (context) =>
context.aggregatedValue.weights
? context.aggregatedValue.weighted / context.aggregatedValue.weights
: undefined,
returnType: 'number',
signatures: ['VWAP{[price], WEIGHT([quantity])}'],
},
}With those definitions all of the following are valid Aggregated Expressions:
RUNNING_SUM{[notional], CUMULATIVE([tradeDate])}
RUNNING_SUM{[notional], GROUP_BY([book]), CUMULATIVE([tradeDate])}
VWAP{[price], WEIGHT([quantity])}
VWAP{[price], WEIGHT([quantity]), GROUP_BY([ticker])} WHERE [status] = 'Filled'Hint
- A
VWAPthat also declarescumulative: trueyields a running VWAP per row: - e.g.
VWAP{[price], WEIGHT([quantity]), CUMULATIVE([tradeDate])}
Note
- The running variant hands each row the accumulator so far and runs
processAggregatedValueon it - As a result, no extra code is required
Hint
The demo at the top of this page uses these RUNNING_SUM and VWAP definitions
- This example uses 4 Custom Aggregated Functions in Calculated Columns:
- Basic —
SUMSQ: sum of squares ofRating—Sum Sq Rating(whole grid) andCcy Sum Sq(grouped byCurrency) - Advanced —
LARGE: Kth largestAmount—2nd Large Amt(usesprocessAggregatedValue) - Advanced —
RANK: rank of eachAmount—Amt Rank(usesprocessAggregatedValueandprepareRowValue) - Advanced —
TOP: true if the row'sAmountis among the K largest —Top 5 Amt(and a hiddenTop 3 Amtused by a Format Column)
- Basic —
- Open the Expression Editor for those Calculated Columns to see the Aggregated Functions
- Sort the
Amountcolumn and note that2nd Large Amt,Amt RankandTop 5 Amtstay correct
Reference
Custom Aggregation Functions are defined using the customAggregatedFunctions property in Expression Options.
customAggregatedFunctions
Default: undefinedRecord<string, AggregatedExpressionFunction>To limit which of the two Aggregated Modules ('Alert' | 'CalculatedColumn') can use a function, set isAvailable — see Custom Expression Function Scope.
Each Aggregated Custom Function is an AggregatedExpressionFunction.
This is a different authoring shape from Standard customs (handler / operands / extends do not apply here).
Deep Dive
Understanding Authoring properties
There are a range of Contexts passed to each Hook.
Deep Dive
Digging into the Contexts passed to each hook
FAQ
Does the Custom Expression Function's name have to be capitalised? No, it is not mandatory, but it is a convention we encourage, as it makes the Expression more readable.
Can I use a custom aggregation in a Row Summary? No. Custom aggregations are Calculated Column Aggregated and Alert Aggregated only.
Is this the same object as a Standard custom function?
No. Standard customs are AdaptableCustomExpressionFunction (handler, returnType, operands).
Aggregated customs are AggregatedExpressionFunction (initialValue, reducer, …).