AdapTable Version:

Framework:

Aggregation Expressions

Summary

Aggregated Expressions are evaluated against grouped data and can return any value
3 specialised use cases are also available:
- Limiting scope of Expression to a group of rows by using the GROUP_BY function
- Performing cumulative aggregations by using the CUMUL and OVER functions
- Creating buckets (e.g. Quartiles, Percentiles) using the QUANT function

Aggregation Scalar Expressions are special Expressions which run against aggregated data.

They can be of 2 types:

Type	Returns	Available in Modules
Scalar	Any value	Calculated Column
Boolean	True or False	Alerts

Find Out More

AdapTable also provides 2 specialist Aggregation Scalar Expressions: Cumulative and Quantile

The Expression evaluates to a single value for each row, but each value is dynamically derived by aggregating multiple rows and columns of the grid.

Scalar Aggregtion Expressions

Scalar Aggregation Expressions are the most basic form of Aggregation.

Note

By default Scalar Aggregated Expressions will compute against the whole grid
See Grouped Aggregation for more advanced used cases

Scalar Aggregation Expressions are of type Aggregated Scalar Query, and require just 2 elements:

`<AGGREGATION_FUNCTION>`

This is the AdapTableQL Aggregated Expression Function used to aggregate the data.

The Aggregation Function can be one of multiple types:

SUM: sums the values of the specified column
PERCENTAGE: calculates the percentage of the specified column

Note

The default denominator for Percentage is the SUM of the specified columns, but this can be overridden

AVG: calculates the average of the specified column
MIN: returns the minimum value of the specified column
MAX: returns the maximum value of the specified column
COUNT: returns the number of items in the specified column
MEDIAN: returns the middle value in the specified column
MODE: returns the most popular item in the specified column
DISTINCT: returns the number of distinct items in the specified column
ONLY: returns the the specified column's value if all are the same
STD_DEVIATION: returns the standard deviation for the specified column

Note

Further Aggregation Functions will be added in future releases in response to user requests
Developers can provide their own Custom Aggregation Functions to supplement this list

`COLUMN`

This is the Adaptable Column that contains the values being aggregated.

Examples

At its most basic just an Aggregation Function and a Column is required:

 // Return sum of all PnL cells
 SUM([PnL])

 // Return the lowest Price in the Grid
 MIN([Price])

The Column that is being aggregated can itself be a Calculated Column (i.e. one which itself is evaluated using an Aggregated Expression)

 // Create a Calculated Column which displays the Price plus VAT
 CalculatedColumns: [{ ColumnId: 'totalPrice', Query: { ScalarExpression: '[price] + [VAT]'}]
 
 // Expression returns sum of all totalPrice Columns i.e. all Prices in the Grid with VAT
  SUM([totalPrice])

The AVG Aggregation Function can receive an optional WEIGHT parameter to create Weighted Averages:

 // Return the Weighted Average for the Price Column (using Index column for weighting)
 AVG([Price], WEIGHT([index]))

AdapTableQL: Scalar Aggregation

Fork Fork

This Demo contains 2 Calculated Columns that use Aggregated Scalar Expressions
- Popularity Ratio
  - displays a Percentage of Popularity Column
  - Popularity is itself a Calculated Column that sums GitHub Stars and GitHub Watchers
- Total Stars
  - shows a Sum of of the GitHub Stars column
  - because GROUP_BY is not added, is the same value in each row

Try It Out

Add a GROUP_BY to the Total Stars Expression to Sum according to Language

Boolean Aggregation Expressions

Boolean Aggregation Expressions (of type Aggregated Boolean Query) are similar to Scalar Aggregation Expressions but only return a true / false value.

Hint

This is ideal for Limits Management and related scenarios
For instance where you want to check that a sum comprising multiple cell values has not been exceeded

In addition to Aggregation Function and Column, 2 addtional elements are typically required:

`<OPERATOR>`

This defines the operator which is used to compare the aggregated value with the threshold.

At present the following comparison operators are supported:

= - Equals
!= - Not Equals
> - Greater Than
< - Less Than
>= - Greater Than or Equals
<= - Less Than or Equals

`<THRESHOLD>`

The Threshold is the (numeric) value that is being compared against the aggregated value. aa In place of absolute numbers, <THRESHOLD> can comprise a number with a string abbreviation.

The following shortcuts can be used:

K: thousand (e.g. '5K')
M: million (e.g. '2M')
B: billion (e.g. '4B')

Caution

When using a threshold shortcut the value must be placed in quotation marks

Examples

 // Is total value of the 'PnL' column in all rows > 5 Million
 SUM([PnL]) > '5000000'

 // Alternate form of above Expression but using abbreviated threshold notation
 SUM([PnL]) > '5M'

 // Is the lowest Bid worth 40,000
 MIN([Bid]) = '40K'

 // Is the average Price over 3 Million
 AVG([Price]) > '3M'

AdapTableQL: Boolean Aggregation

Fork Fork

This demo contains 3 Alerts that fire when an Aggregated Boolean Expression is triggered
- if the lowest Github Watchers values is under 50
- when the total of all the Github Stars rows is over 10,000
- when the total of all Open Issues rows is over 6,000

Try It Out

In the first row change the value of:
- Github Watchers column to 45 and see the Info Alert be fired
- Github Stars column to 279429 and see the Success Alert be fired
- Open Issues column to 1220 and see the Warning Alert be fired

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Boolean Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['Alert'],
      Tabs: [
        {
          Name: 'Toolbars',
          Toolbars: ['Alert', 'CellSummary'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Alert'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Alert: {
      AlertDefinitions: [
        {
          Name: 'alert-Info-44',
          MessageType: 'Info',
          Scope: {
            All: true,
          },
          Rule: {
            AggregatedBooleanExpression: 'MIN([github_watchers] ) < 50 ',
          },
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Success-45',
          MessageType: 'Success',
          Scope: {
            All: true,
          },
          Rule: {
            AggregatedBooleanExpression: "SUM([github_stars] )  > '10k' ",
          },
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Warning-46',
          MessageType: 'Warning',
          Scope: {
            All: true,
          },
          Rule: {
            AggregatedBooleanExpression: "SUM([open_issues_count] ) >'6k'",
          },
          AlertProperties: {
            DisplayNotification: true,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_watchers',
            'license',
            'github_stars',
            'open_issues_count',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Grouped Aggregation

An Aggregation Scalar Function can be applied just to a group of rows, rather than to the entire grid.

This is done by using the GROUP_BY AdapTableQL Expression Function.

Note

The GROUP_BY function is always only used together with an Aggregation Function

The GROUP_BY function typically receives a single parameter - the name of a Column:

 // Return Sum of all Pnl cells for rows which have this row's Currency value
 SUM([PnL], GROUP_BY([Currency]) )

It is particularly useful when used with the COUNT function to give the numbers in each group:

 // Return count of all rows in the Grid which have the same Currency as this one
 COUNT([Currency], GROUP_BY([Currency]) )

For more advanced use cases, the GROUP_BY function can receive multiple Columns:

 // Return Sum of all Pnl cells for rows which have this row's Currency and Counterparty values
 SUM([PnL], GROUP_BY([Currency], [Counterparty]) )

Deep Dive

How Grouped Aggregation Works

AdapTableQL: Grouped Aggregation

Fork Fork

This Demo contains 4 Calculated Columns that use Aggregated Scalar Expressions with Grouping
- Watchers By Lang - Sum of of the GitHub Watchers column, grouped by Language
- Avg. Lang Popularity - Average of Github Stars popularity, grouped by language
- Open/Closed Issues % by Lang - Percentage of Open and Closed issues, grouped by language
- Lang Count - Count of each language, also grouped language

Expand to see the Aggregated and Grouped Scalar Expressions

The 3 Calculated Columns are defined as follows:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'total-watchers-by-lang',
      FriendlyName: 'Watchers By Lang',
      Query: {
        AggregatedScalarExpression: 'Sum([github_watchers])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'average-popularity-by-lang',
      FriendlyName: 'Avg. Popularity by Lang',
      Query: {
        AggregatedScalarExpression: 'AVG([github_stars], GROUP_BY([language]))',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'percentage-issues-by-lang',
      FriendlyName: 'Open/Closed Issues % by Lang',
      Query: {
        AggregatedScalarExpression: 'PERCENTAGE([open_issues_count], SUM([closed_issues_count], GROUP_BY([language]))) ',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'lang-count',
      FriendlyName: 'Lang Count',
      Query: {
        AggregatedScalarExpression: 'COUNT([language], GROUP_BY([language])) ',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
]}

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Scalar GroupBy Expressions',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'lang-count',
            'github_watchers',
            'github_stars',
            'total-watchers-by-lang',
            'average-popularity-by-lang',
            'percentage-issues-by-lang',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'total-watchers-by-lang',
          FriendlyName: 'Watchers By Lang',
          Query: {
            AggregatedScalarExpression:
              'Sum([github_watchers], GROUP_BY([language]))',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'average-popularity-by-lang',
          FriendlyName: 'Avg. Lang Popularity',
          Query: {
            AggregatedScalarExpression:
              'AVG([github_stars], GROUP_BY([language]))',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'percentage-issues-by-lang',
          FriendlyName: 'Open/Closed Issues % by Lang',
          Query: {
            AggregatedScalarExpression:
              'PERCENTAGE([open_issues_count], SUM([closed_issues_count], GROUP_BY([language]))) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'lang-count',
          FriendlyName: 'Lang Count',
          Query: {
            AggregatedScalarExpression:
              'COUNT([language], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
  },
};

`WHERE` Clause

Optionally, a WHERE clause may be appended to an Aggregated Boolean Expression.

This is used to narrow down the scope of the 'main' part of the Expression.

The WHERE Clause takes the form of a Standard Expression.

 // Are all PnL values in rows with Dollar Currency over 5M
 SUM([PnL]) > '5M' WHERE [Currency] = 'USD'

 // Similar Expression to above but with more complex WHERE Clause
 SUM([PnL]) > '5M' WHERE [Currency] = 'USD' AND [RequiredDate] < ADD_DAYS(CURRENT_DAY, 30)

AdapTableQL: Boolean Aggregations with WHERE

Fork Fork

This demo contains 2 Alerts that fire when an Aggregated Boolean Expression is triggered.
Both are the same Aggregation Boolean Expressions as in the demo above but with a WHERE clause added:
- when the total of Github Stars is over 10,000 - only for rows where Licence is 'MIT License'
- when the total of Open Issues is over 6,000 - only for rows where Language is 'JavaScript'

Try It Out

In the first row change the value of:
- Github Stars column to 279429 and see the Success Alert be fired (because Licence in first row is "MIT License")
- Open Issues column to 1220 and see the Warning Alert be fired (because Language in first row is "JavaScript")

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Boolean Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['Alert'],
      Tabs: [
        {
          Name: 'Toolbars',
          Toolbars: ['Alert', 'CellSummary'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Alert'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Alert: {
      AlertDefinitions: [
        {
          Name: 'alert-Success-47',
          MessageType: 'Success',
          Scope: {
            All: true,
          },
          Rule: {
            AggregatedBooleanExpression:
              "SUM([github_stars] )  > '10k' WHERE [license] = 'MIT License'",
          },
          AlertProperties: {
            DisplayNotification: true,
          },
        },
        {
          Name: 'alert-Warning-48',
          MessageType: 'Warning',
          Scope: {
            All: true,
          },
          Rule: {
            AggregatedBooleanExpression:
              "SUM([open_issues_count] ) >'6k' WHERE [language] = 'JavaScript' ",
          },
          AlertProperties: {
            DisplayNotification: true,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_watchers',
            'license',
            'github_stars',
            'open_issues_count',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

FAQ

Can Aggregated Scalar Functions reference Calculated Columns Yes, this was added in AdapTable Version 12.1

Does Aggregation take Pagination into account? No. All the data in the AG Grid data source is used when aggregating and not solely what is on the current page.

AdapTable Resources

PreviousStandard Expressions NextCumulative Expressions

Framework: