Aggregated Custom Expression Functions

View as Markdown

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 SUM or AVG
  • Declare capability, get feature: cumulative: true gives running value per row; weight:true gives WEIGHT([col])
  • Grouping and filtering for free: every Custom Aggregation accepts GROUP_BY and WHERE
  • Available to Users: listed in Expression Editor with your description, signatures and examples
  • One small object: an initialValue and a reducer - 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

AdapTableQL: Custom Aggregation Capabilities
Fork
  • This demo defines 2 Custom Aggregated Functions (explained in Declared Capabilities below):
    • RUNNING_SUM - declares cumulative: true
    • VWAP - declares weight: true and cumulative: 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])}
Try It Out
  • Sort by Trade Date (the default) and see Running Notional build up trade by trade
  • Change the Quantity of an AAPL trade and watch both VWAP Columns move for every AAPL trade
  • Open the Expression Editor for a Calculated Column to see RUNNING_SUM and VWAP listed with their clauses
Loading demo…

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:

LevelPropertiesReturns
BasicinitialValue, reducerOne value for the set (or for each group)
AdvancedBasic, plus processAggregatedValue and / or prepareRowValueOne 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 accumulator
  • reducer — 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's SUMSQ)
  • 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}'],
    },
  },
}
1
Add the function to Expression Options

Add each function to expressionOptions.customAggregatedFunctions.

This is a Record (or a callback that returns one):

2
Set initialValue

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

3
Provide the reducer

Called for each row in the set; returns the new accumulator.

The AggregatedExpressionReducerContext includes:

  • accumulator — previous return value, or initialValue on the first call
  • currentValue — the aggregated Column's value in this row
  • index — position in the Column data
  • args — any extra args, e.g. threshold in COUNT_ABOVE{[amount], 1000}
  • aggColumnId, getValueForColId, rowNode, groupByColumnIds
4
Provide editor help

Shown in the Expression Editor:

  • inputTypes — optional argument types (helps editor cue suitable Columns)
  • description — what the Function does
  • signatures — how it should be called
  • examples — small Expressions that use it
  • category — 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

HookRunsUse it toExample
processAggregatedValueOnce, after every row has been reducedTurn the reducer result into the final value (sort, pick Kth, build a lookup)LARGE
prepareRowValuePer row, after processAggregatedValueReturn 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 in processAggregatedValue
  • Then build a Map / Set so prepareRowValue is a cheap lookup per row
Developer Guide

Defining Advanced Custom Aggregated Functions

This example creates 2 functions:

  • LARGE — the Kth largest value (like Excel's LARGE); uses processAggregatedValue
  • RANK — the rank of each row's value (1 = smallest); uses processAggregatedValue and prepareRowValue
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]}'],
    },
  },
}
1
Collect values in the reducer

Both functions just collect the values; the real work happens afterwards.

Note the factory initialValue: () => [], because the reducer mutates the array.

2
Process the aggregate (once)

processAggregatedValue runs once, after every row has been reduced. Whatever it returns becomes the aggregated value.

  • LARGE sorts once and returns the Kth value (K arrives in context.args)
  • RANK sorts once and returns a Map of value → rank

AggregatedExpressionProcessAggValueContext includes aggregatedValue (the reducer result), rowNodes (the rows in this set / group) and args.

3
Prepare a value per row

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.

4
Optionally filter rows

filterRow runs before the reducer; return false to leave a row out of the aggregation.

It receives AggregatedExpressionFilterRowContext.

Hint

  • prepareRowValue can also return a boolean - see the demo's TOP function
  • it returns true when 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.

PropertyEnablesNotes
cumulative: trueCUMULATIVE([orderCol]) — a running value per rowThe 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 projectionUse 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: trueWEIGHT([weightCol])The weight Column's value for the current row arrives as weightValue in the reducer, prepareRowValue, filterRow and cumulative.currentValue contexts.
returnTypeStatic 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 CUMULATIVE or WEIGHT on 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 VWAP that also declares cumulative: true yields 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 processAggregatedValue on 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

AdapTableQL: Custom Aggregation Functions
Fork
  • This example uses 4 Custom Aggregated Functions in Calculated Columns:
    • Basic — SUMSQ: sum of squares of Rating — Sum Sq Rating (whole grid) and Ccy Sum Sq (grouped by Currency)
    • Advanced — LARGE: Kth largest Amount — 2nd Large Amt (uses processAggregatedValue)
    • Advanced — RANK: rank of each Amount — Amt Rank (uses processAggregatedValue and prepareRowValue)
    • Advanced — TOP: true if the row's Amount is among the K largest — Top 5 Amt (and a hidden Top 3 Amt used by a Format Column)
Try It Out
  • Open the Expression Editor for those Calculated Columns to see the Aggregated Functions
  • Sort the Amount column and note that 2nd Large Amt, Amt Rank and Top 5 Amt stay correct
Loading demo…

Reference

Custom Aggregation Functions are defined using the customAggregatedFunctions property in Expression Options.

customAggregatedFunctions

Default: undefined
Record<string, AggregatedExpressionFunction>
Custom Aggregated Expression Functions available in AdapTableQL

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, …).