AdapTable Version:

Framework:

Aggregated Custom Expression Functions

Summary

Aggregated Custom Expression Functions work on an array of data and return a single value
This allows users to create Functions that operate on multiple rows or an entire column

Developers are able to provide Custom Aggregation Expression Functions to AdapTableQL.

These are functions which operate against an array of data and return a single value.

Note

The array of data provided to the Expression is often a column name, but this does not have to be the case

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

customAggregatedFunctions

Record<string, AggregatedExpressionFunction>

Custom Aggregated Expression Functions available in AdapTableQL

This property provides Custom Aggregated Expression Functions to be added to AdapTableQL; it has the signature:

 customAggregatedFunctions?:
    | Record<string, AggregatedExpressionFunction>
    | ((context: GlobalExpressionFunctionsContext<string>) => Record<string, AggregatedExpressionFunction>);

The property returns a record of type string and AggregatedExpressionFunction either directly or via a function.

Deep Dive

Anatomy of an Custom Aggregated Expression Function

A Custom Aggregated Function is of type AggregatedExpressionFunction which is defined as follows:

Property	Description
description	Description of the Aggregated Expression Function
examples	Examples to use the Aggregated Expression Function
filterRow	Optional filter which is applied to each row before the reducer is called. If the filter returns false, the row is NOT included in the aggregation.
initialValue	Mandatory Initial Value for the aggregation
inputTypes	Optional argument types.
prepareRowValue	Called for each row AFTER the reducer() and processAggregatedValue() functions Can be used when each row has a different aggregated value (e.g. percentage = row value / aggregated value). Returns return value for each individual row
processAggregatedValue	Called AFTER the reducer() has processed all values / rows Can be used to change result of reducer based on the values array (e.g. average = aggregated value / count)
reducer	Mandatory Reducer function which is called for each value(row) in the Column Data
signatures	Example Signatures for the Aggregated Expression Function

As can be seen there are a number of properties which can provided in the Expression Function definition.

Here we will list some of the most important.

Note

2 props - initialValue and reducer - are mandatory; the others are available for advanced use cases
Many of these properties are functions which receive a unique rich, context object to assist in the evaluation

initialValue (mandatory)

The initialValue property provides the start value for the aggregation.

reducer (mandatory)

This is the main function which performs the evaluation on each element in the data, can return any value.

It receives context of type AggregatedExpressionReducerContext, which is defined as follows:

Property	Description
accumulator	Value returned by the previous call to the reducer function, or the initialValue if this is the first call. You return the new accumulator value from this function.
aggColumnId	Column Id of the column being aggregated
args	Arguments passed to the function
currentValue	Current value being processed in the Column Data
getValueForColId	Helper function to get the value of a column
groupByColumnIds	Column Id(s) of the column(s) being grouped by
index	Index of the current value in the Column Data
rowNode	Current row node
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

For more complicated scenarios, 4 additional, optional, functions can be provided:

processAggregatedValue

This function is used to change the result of the Aggregated value.

It is called after the reducer has processed all values / rows.

It receives context of type AggregatedExpressionProcessAggValueContext, which is defined as follows:

Property	Description
aggColumnId	Column Id of the column being aggregated
aggregatedValue	Result of the reducer
args	Arguments passed to the function
groupByColumnIds	Column Id(s) of the column(s) being grouped by
rowNodes	Array of row nodes; if aggregation is grouped, this will be the array of row nodes for the group
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

prepareRowValue

This function can be used when each row has a different aggregated value (e.g. percentage = row value / agg value).

It is called for each row after the reducer and processAggregatedValue functions.

It returns the return value for each individual row.

It receives context of type AggregatedExpressionPrepareRowValueContext, which is defined as follows:

Property	Description
aggColumnId	Column Id of the column being aggregated
aggregatedValue	Result of the reducer
args	Arguments passed to the function
getValueForColId	Helper function to get the value of a column
groupByColumnIds	Column Id(s) of the column(s) being grouped by
rowNode	Current Row Node
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

filterRow

This function provides a filter which is applied to each row before the reducer is called.

If the filter returns false, the row is not included in the aggregation.

It receives context of type AggregatedExpressionFilterRowContext, which is defined as follows:

Property	Description
aggColumnId	Column Id of the column being aggregated Usually the first Col argument, e.g. [colId]
args	Arguments passed to the function
getValueForColId	Utility function to get the value of a column
groupByColumnIds	Column Id(s) of the column(s) being grouped by
rowNode	Current row node
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

args

All the Context objects include an args property in which column arguments are transformed to their values.

For example: [colId] -> args = [value-of-colId]

Caution

Aggregation Column and "Group By" Columns are not included as they are passed as separate arguments

getValueForColId

Another property available in most Context objects is getValueForColId.

This is a very useful utility function to get the value of a column and has this signature:

getValueForColId: (colId: string) => any;

Developer Guide

Defining a Custom Aggregated Expression Function

In this Guide we create 4 Custom Aggregated Expression Functions:

PRODUCT - a straightforwrd function that returns the product of a series of numbers
SUMSQ - a straightforwrd function that Mimics SUMSQL in Excel, returning sum of squares of numbers
RANK - returns the rank of a number in a list - leverages more advanced properties available in the Function definition
LARGE - returns the Kth biggest number in a list (where K is an input) - also leverages advanced Function definition properties

expressionOptions: {
  customAggregatedFunctions: {
    PRODUCT: {
      initialValue: 1,
      reducer: (context: AggregatedExpressionReducerContext) => {
        return context.accumulator * context.currentValue;
      },
      description: 'Returns product of an array of values',
      signatures: ['PRODUCT(numericVals: number[])'],
      examples: ['PRODUCT([price])'],
    },
   SUMSQ: {
      initialValue: 1,
      reducer: (context: AggregatedExpressionReducerContext) => {
        return context.accumulator + context.currentValue * 2;
      },
      description:
        'Mimics SUMSQL in Excel - returning sum of squares of arguments',
      signatures: ['SUMSQ(numericVals: number[])'],
      examples: ['SUMSQ([price])'],
    },
    RANK: {
      initialValue: [],
      reducer: (context: AggregatedExpressionReducerContext) => {
        if (context.currentValue != undefined) {
          context.accumulator.push(context.currentValue);
        }
        return context.accumulator;
      },
      processAggregatedValue: (
        context: AggregatedExpressionProcessAggValueContext
      ) => {
        const sortedDataCollection = context.aggregatedValue.sort(
          (firstValue: any, secondValue: any) => firstValue - secondValue
        );
        const rankMap = new Map<any, number>();
        sortedDataCollection.forEach((value: any, index: any) => {
          rankMap.set(value, index + 1);
        });
        return rankMap;
      },
      prepareRowValue: (
        context: AggregatedExpressionPrepareRowValueContext
      ) => {
        const aggregatedColumnId = context.aggColumnId;
        const rowValue = context.adaptableApi.gridApi.getRawValueFromRowNode(
          context.rowNode,
          aggregatedColumnId
        );
        return context.aggregatedValue.get(rowValue) as number;
      },
      description: 'Mimics RANK in Excel; returns rank of a number in given list',
      signatures: ['RANK([amount])'],
      examples: ['RANK([amount])'],    
    },
    LARGE: {
      initialValue: [],
      reducer: (context: AggregatedExpressionReducerContext) => {
        if (context.currentValue != undefined) {
          context.accumulator.push(context.currentValue);
          context.accumulator.sort((a: any, b: any) => b - a);
        }
        return context.accumulator;
      },
      processAggregatedValue: (
        context: AggregatedExpressionProcessAggValueContext
      ) => {
        const k = context.args[0];
        if (k == undefined || k <= 0 || k > context.rowNodes.length) {
          console.log('Invalid K value for LARGE function: ', k);
          return;
        }
        return context.aggregatedValue[k - 1];
      },
      description: 'Mimics LARGE in Excel; returns Kth largest number in list',
      signatures: ['LARGE([price], K: number)'],
      examples: ['LARGE([price], 4)'],
    },
}

Add function to Expression Options and specify a name

Add the Custom Expression Function definition to the customAggregatedFunctions section in Expression Options

Each entry is of type Record with the key and value as follows:

key is the custom Function's name (by convention, it is in CAPITAL LETTERS - to make it more readable when used in an Expression)
value is the Function's definition (which is of type: AggregatedExpressionFunction)

Provide the Initial Value (mandatory)

Use the initialValue property to set the start value for the aggregation

Provide the reducer function (mandatory)

This function will be called for each value (typically a row) in the Column Data

The context object provided to the function includes:

accumulator - value returned by the previous call to the reducer function, or the initialValue if this is the first call. You return the new accumulator value from this function
currentValue - current value being processed in Column Data
index - index of current value in the Column Data

Provide a processAggregatedValue function

Called after the reducer has processed all values / rows

Can be used to change result of reducer based on the values array (e.g. average = aggregated value / count)

The function context includes:

aggregatedValue - result of the reducer
rowNodes - array of row nodes (if aggregation was grouped, this will be array of row nodes for the group)

Provide a prepareRowValue function

Called for each row after the reducer and processAggregatedValue functions.

Can be used when each row has a different aggregated value (e.g. percentage = row value / aggregated value)

The function context inlucdes the current row node.

Provide Help for run-time Users

There are 3 properties that should be provided in order to help Users accessing the Function in the Expression Editor:

description: Explains what the Function does
signatures: Shows how the Function should be called
examples: Small examples of Function in action

AdapTableQL: Custom Aggregation Functions

Fork Fork

This example creates the 4 Custom Aggregation Expression Functions in the Developer Steps above, and references them in various Calculated Columns:
- PRODUCT: multiplies set of values - used in Total Rating (product of Rating Column) & Ccy Rating (product of Rating grouped by Currency)
- SUMSQ: return sum of squares of values - used in Sum Sq Rating (operates on Rating Column)
- LARGE: returns the K-th largest value in a data set - used in 2nd largest amount (2nd largest value in Amount Column)
- RANK: returns the rank of a number in a list of numbers - used in Amt Rank (ranks the Amount Column)
Note: both LARGE and RANK require data sorting; however, RANK outperforms LARGE by leveraging Adaptable's provided hooks to sort the data only once and then using a map for quick lookups, resulting in a significant performance boost

Expand to see the Custom Aggregation Functions provided

The Custom Aggregation Expression Functions are defined as follows:

expressionOptions: {
  customAggregatedFunctions: {
    PRODUCT: {
      initialValue: 1,
      reducer: (context: AggregatedExpressionReducerContext) => {
        return context.accumulator * context.currentValue;
      },
      description: 'Returns product of an array of values',
      signatures: ['PRODUCT(numericVals: number[])'],
      examples: ['PRODUCT([price])'],
    },
    SUMSQ: {
      initialValue: 1,
      reducer: (context: AggregatedExpressionReducerContext) => {
        return context.accumulator + context.currentValue * 2;
      },
      description:
        'Mimics SUMSQL in Excel - returning sum of squares of arguments',
      signatures: ['SUMSQ(numericVals: number[])'],
      examples: ['SUMSQ([price])'],
    },
    LARGE: {
      description:
        'Mimics LARGE in Excel - Returns the K-th largest value in a data set',
      signatures: ['LARGE([price], K: number)'],
      examples: ['LARGE([price], 4)'],
      initialValue: [],
      reducer: (context: AggregatedExpressionReducerContext) => {
        if (context.currentValue != undefined) {
          context.accumulator.push(context.currentValue);
          context.accumulator.sort((a: any, b: any) => b - a);
        }
        return context.accumulator;
      },
      processAggregatedValue: (
        context: AggregatedExpressionProcessAggValueContext
      ) => {
        const k = context.args[0];
        if (k == undefined || k <= 0 || k > context.rowNodes.length) {
          console.log('Invalid K value for LARGE function: ', k);
          return;
        }

        // return the value of the aggregated column for the k-th row node (k-1 because of 0-based index)
        return context.aggregatedValue[k - 1];
      },
    },
    RANK: {
      description:
        'Mimics RANK in Excel - Returns the rank of a number in a list of numbers',
      signatures: ['RANK([amount])'],
      examples: ['RANK([amount])'],
      initialValue: [],
      reducer: (context: AggregatedExpressionReducerContext) => {
        // sorting an array is an O(n log n) operation, and doing it for every element would result in an O(n^2 log n) operation
        // a more efficient way is to sort the array after all elements have been added, in the processAggregatedValue() function
        if (context.currentValue != undefined) {
          context.accumulator.push(context.currentValue);
        }
        return context.accumulator;
      },
      processAggregatedValue: (
        context: AggregatedExpressionProcessAggValueContext
      ) => {
        // 1. sort the array and populate the rank map
        const sortedDataCollection = context.aggregatedValue.sort(
          (firstValue: any, secondValue: any) => firstValue - secondValue
        );

        // 2. map sorted array to a rank map
        const rankMap = new Map<any, number>();
        sortedDataCollection.forEach((value: any, index: any) => {
          rankMap.set(value, index + 1);
        });

        // this aggregatedValue will be passed to prepareRowValue() function
        return rankMap;
      },
      prepareRowValue: (
        context: AggregatedExpressionPrepareRowValueContext
      ) => {
        // this function will be called for each row after the reducer() and processAggregatedValue() functions
        // because the aggregated value is a map, we can get the rank of the current row value with a single lookup (O(1) complexity)
        const aggregatedColumnId = context.aggColumnId;
        const rowValue = context.adaptableApi.gridApi.getRawValueFromRowNode(
          context.rowNode,
          aggregatedColumnId
        );

        return context.aggregatedValue.get(rowValue) as number;
      },
    },
  },
},

And they are referenced in these Calculated Columns:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'total_rating',
      Query: {
        AggregatedExpression: 'PRODUCT([rating]) ',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
      FriendlyName: 'Total Rating',
    },
    {
      ColumnId: 'currency_rating',
      Query: {
        AggregatedExpression: 'PRODUCT([rating], GROUP_BY([currency] ) ) ',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
      FriendlyName: 'Ccy Rating',
    },
    {
      ColumnId: 'sum_square_rating',
      Query: {
        AggregatedExpression: 'SUMSQ([rating])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
      FriendlyName: 'Sum Sq Rating',
    },{
      ColumnId: '2nd_largest_amount',
      Query: {
        AggregatedScalarExpression: 'LARGE([amount], 2)',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
      FriendlyName: '2nd Large Amt',
    },
    {
      ColumnId: 'rank_amount',
      Query: {
        AggregatedScalarExpression: 'RANK([amount])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
      FriendlyName: 'Amt Rank',
    },
  ],
},

Try It Out

Open the Expression Editor for the Calculated Columns to see the Expressions and the Aggregated Expression Functions
Sort the Amount column to and note how the 2nd Large Amt and Ranking of Amount are both correct

import {
  AdaptableOptions,
  //AggregatedExpressionReducerContext,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';
import {
  AggregatedExpressionPrepareRowValueContext,
  AggregatedExpressionProcessAggValueContext,
  AggregatedExpressionReducerContext,
} from '@adaptabletools/adaptable/src/parser/src/types';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Custom Aggregation Expression Functions',
  expressionOptions: {
    customAggregatedFunctions: {
      PRODUCT: {
        initialValue: 1,
        reducer: (context: AggregatedExpressionReducerContext) => {
          return context.accumulator * context.currentValue;
        },
        description: 'Returns product of an array of values',
        signatures: ['PRODUCT(numericVals: number[])'],
        examples: ['PRODUCT([price])'],
      },
      SUMSQ: {
        initialValue: 1,
        reducer: (context: AggregatedExpressionReducerContext) => {
          return context.accumulator + context.currentValue * 2;
        },
        description:
          'Mimics SUMSQL in Excel - returning sum of squares of arguments',
        signatures: ['SUMSQ(numericVals: number[])'],
        examples: ['SUMSQ([price])'],
      },
      LARGE: {
        initialValue: [],
        reducer: (context: AggregatedExpressionReducerContext) => {
          if (context.currentValue != undefined) {
            context.accumulator.push(context.currentValue);
            context.accumulator.sort((a: any, b: any) => b - a);
          }
          return context.accumulator;
        },
        processAggregatedValue: (
          context: AggregatedExpressionProcessAggValueContext
        ) => {
          const k = context.args[0];
          if (k == undefined || k <= 0 || k > context.rowNodes.length) {
            console.log('Invalid K value for LARGE function: ', k);
            return;
          }

          // return the value of the aggregated column for the k-th row node (k-1 because of 0-based index)
          return context.aggregatedValue[k - 1];
        },
        description:
          'Mimics LARGE in Excel - Returns the K-th largest value in a data set',
        signatures: ['LARGE([price], K: number)'],
        examples: ['LARGE([price], 4)'],
      },
      RANK: {
        description:
          'Mimics RANK in Excel - Returns the rank of a number in a list of numbers',
        signatures: ['RANK([amount])'],
        examples: ['RANK([amount])'],
        initialValue: [],
        reducer: (context: AggregatedExpressionReducerContext) => {
          // sorting an array is an O(n log n) operation, and doing it for every element would result in an O(n^2 log n) operation
          // a more efficient way is to sort the array after all elements have been added, in the processAggregatedValue() function
          if (context.currentValue != undefined) {
            context.accumulator.push(context.currentValue);
          }
          return context.accumulator;
        },
        processAggregatedValue: (
          context: AggregatedExpressionProcessAggValueContext
        ) => {
          // 1. sort the array and populate the rank map
          const sortedDataCollection = context.aggregatedValue.sort(
            (firstValue: any, secondValue: any) => firstValue - secondValue
          );

          // 2. map sorted array to a rank map
          const rankMap = new Map<any, number>();
          sortedDataCollection.forEach((value: any, index: any) => {
            rankMap.set(value, index + 1);
          });

          // this aggregatedValue will be passed to prepareRowValue() function
          return rankMap;
        },
        prepareRowValue: (
          context: AggregatedExpressionPrepareRowValueContext
        ) => {
          // this function will be called for each row after the reducer() and processAggregatedValue() functions
          // because the aggregated value is a map, we can get the rank of the current row value with a single lookup (O(1) complexity)
          const aggregatedColumnId = context.aggColumnId;
          const rowValue = context.adaptableApi.gridApi.getRawValueFromRowNode(
            context.rowNode,
            aggregatedColumnId
          );

          return context.aggregatedValue.get(rowValue) as number;
        },
      },
    },
  },

  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'total_rating',
          Query: {
            AggregatedScalarExpression: 'PRODUCT([rating])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
          FriendlyName: 'Total Rating',
        },
        {
          ColumnId: 'currency_rating',
          Query: {
            AggregatedScalarExpression:
              'PRODUCT([rating], GROUP_BY([currency]))',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
          FriendlyName: 'Ccy Rating',
        },
        {
          ColumnId: 'sum_square_rating',
          Query: {
            AggregatedScalarExpression: 'SUMSQ([rating])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
          FriendlyName: 'Sum Sq Rating',
        },
        {
          ColumnId: '2nd_largest_amount',
          Query: {
            AggregatedScalarExpression: 'LARGE([amount], 2)',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
          FriendlyName: '2nd Large Amt',
        },
        {
          ColumnId: 'rank_amount',
          Query: {
            AggregatedScalarExpression: 'RANK([amount])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
          FriendlyName: 'Amt Rank',
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'item',
            'amount',
            'rank_amount',
            '2nd_largest_amount',
            'currency',
            'rating',
            'total_rating',
            'currency_rating',
            'sum_square_rating',
          ],
          AutoSizeColumns: true,
          //  RowSummaries: [
          //    {
          //      Position: 'Top',
          //      ColumnsMap: {
          //        rating: 'PRODUCT',
          //      },
          //    },
          //  ],
          // - Note: We have also used `PRODUCT` in a <HandbookLink name="Row Summary" link="handbook-summarising-rows"/> for the `Rating` Column
        },
      ],
    },
  },
};

PreviousCustom Standard Functions NextCustom Functions Scope

Framework: