AdapTable Version:

Framework:

Aggregated Calculated Columns

Summary

Aggregated Calculated Columns show a value that is derived dynamically from multiple, distinct rows

Most Calculated Columns use a Standard Scalar, basing the calculated value on other cells in the row.

However Calculated Column values can also be derived dynamically from multiple, distinct rows.

In this use case the Expression is of type Aggregation.

Like a Standard Scalar Expression, the Aggregated Scalar Expression returns a single scalar value.

Note

Aggregated Scalar Expressions can accept Scalar Expressions as inputs

Each value returned is calculated by aggregating multiple rows and columns in the grid.

Hint

A Calculated Column's Aggregated Scalar Expression updates in real time when a referenced grid value changes

Aggregation Operations

The following Aggregation operations are supported by AdapTableQL:

SUM: sums the values of the specified columns
PERCENTAGE: calculates the percentage of the specified columns (using SUM as default)
AVG: calculates average of specified columns (optional WEIGHT parameter supports Weighted Averages)
MIN: returns the minimum value of the specified columns
MAX: returns the maximum value of the specified columns

Caution

Aggregated Calculated Columns cannot be used when using the Server-Side Row Model
This is because they might not have all the rows on the client when performing the calculation

Calculated Columns: Aggregated

Fork Fork

This demo contains 5 Calculated Columns that use Aggregation Expressions - all on the Github Watchers Column:
- Sum Watchers - sums all the values
- Average Watchers - average of all the values
- Min Watchers - Minimum of the values
- Max Watchers - Maximum of the values
- % Watchers - Percentage of the values
Note these Calculated Columns do not use grouping - see demo below for an example using Groups

Expand to see the Aggregation Scalar Expressions

CalculatedColumn: {
 CalculatedColumns: [
    {
      ColumnId: 'sum-watchers',
      FriendlyName: 'Sum Watchers',
      Query: { AggregatedScalarExpression: 'SUM([github_watchers])' },
      CalculatedColumnSettings: {
        DataType: 'number',
        Resizable: true,
      },
    },
    {
      ColumnId: 'average-watchers',
      FriendlyName: 'Avg. Watchers',
      Query: { AggregatedScalarExpression: 'AVG([github_watchers])' },
      CalculatedColumnSettings: {
        DataType: 'number',
        Resizable: true,
      },
    },
    {
      ColumnId: 'min-watchers',
      FriendlyName: 'Min Watchers',
      Query: { AggregatedScalarExpression: 'MIN([github_watchers])' },
      CalculatedColumnSettings: {
        DataType: 'number',
        Resizable: true,
      },
    },
    {
      ColumnId: 'max-watchers',
      FriendlyName: 'Max Watchers',
      Query: { AggregatedScalarExpression: 'MAX([github_watchers])' },
      CalculatedColumnSettings: {
        DataType: 'number',
        Resizable: true,
      },
    },
    {
      ColumnId: 'percentage-watchers',
      FriendlyName: '% Watchers',
      Query: {
        AggregatedScalarExpression: 'PERCENTAGE([github_watchers])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Resizable: true,
      },
    },
  ],
},

Try It Out

Update a cell in the Github Watchers column and see how all 5 Calculated Columns update

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
      PinnedToolbars: ['Layout'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'language',
            'github_watchers',
            'sum-watchers',
            'average-watchers',
            'min-watchers',
            'max-watchers',
            'percentage-watchers',
            'name',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'sum-watchers',
          FriendlyName: 'Sum Watchers',
          Query: {
            AggregatedScalarExpression: 'SUM([github_watchers])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
        {
          ColumnId: 'average-watchers',
          FriendlyName: 'Avg. Watchers',
          Query: {
            AggregatedScalarExpression: 'AVG([github_watchers])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
        {
          ColumnId: 'min-watchers',
          FriendlyName: 'Min Watchers',
          Query: {
            AggregatedScalarExpression: 'MIN([github_watchers])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
        {
          ColumnId: 'max-watchers',
          FriendlyName: 'Max Watchers',
          Query: {
            AggregatedScalarExpression: 'MAX([github_watchers])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
        {
          ColumnId: 'percentage-watchers',
          FriendlyName: '% Watchers',
          Query: {
            AggregatedScalarExpression: 'PERCENTAGE([github_watchers])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-sum-watchers',
          Scope: {
            ColumnIds: [
              'sum-watchers',
              'average-watchers',
              'min-watchers',
              'max-watchers',
              'percentage-watchers',
            ],
          },
          Style: {
            FontStyle: 'Italic',
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Using `GROUP_BY`

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

This is done by using the GROUP_BY AdapTableQL Expression Function.

This allows for much more powerful Expressions than displayed in the example above, becauase you can have multiple sets of Aggregations.

Find Out More

See the AdapTableQL Guide to Aggregation Expressions for more information and examples

Calculated Columns: Aggregated with GROUP_BY

Fork Fork

This demo contains 3 Calculated Columns that use Aggregation Expressions
All are based on the Github Watchers Column and all use grouping on the Language Column:
- Sum Watchers - sums all the values
- Average Watchers - average of all the values
- % Watchers - Percentage of the values
It also contains another Aggregated Scalar Calculated Column - Open/Closed Issues Percentage by Language
- This calculates the percentage of open and closed issues of all the repos in the grid, grouped by language
- It's a slightly contrived example, but showcases how to calculate a percentage based on aggregated values from other columns

Expand to see the Aggregation Scalar Expressions

CalculatedColumn: {
  CalculatedColumns: [
    {
      FriendlyName: 'Popularity',
      ColumnId: 'githubPopularity',
      Query: { ScalarExpression: '[github_watchers] + [github_stars]' },
    },
    {
      FriendlyName: 'Subscribers Ratio',
      ColumnId: 'subscribersRatio',
      Query: { AggregatedScalarExpression: 'PERCENTAGE([github_watchers])', },
    },
    {
      ColumnId: 'average-popularity-by-lang',
      FriendlyName: 'Avg. Popularity by Lang',
      Query: { AggregatedScalarExpression: 'AVG([githubPopularity], 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' },
    },
  ],
},

Try It Out

Update a cell in the Github Watchers column and see how the Calculated Columns update

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated GroupBy Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
      PinnedToolbars: ['Layout'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'language',
            'github_watchers',
            'sum-watchers-by-lang',
            'avg-watchers-by-lang',
            'percentage-watchers-by-lang',
            'percentage-issues-by-lang',
            'name',
          ],
        },
        {
          Name: 'Grouped',
          TableColumns: [
            'language',
            'github_watchers',
            'sum-watchers-by-lang',
            'avg-watchers-by-lang',
            'percentage-watchers-by-lang',
            'percentage-issues-by-lang',
            'name',
          ],
          RowGroupedColumns: ['language'],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'sum-watchers-by-lang',
          FriendlyName: 'Sum Watchers by Lang',
          Query: {
            AggregatedScalarExpression:
              'SUM([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'avg-watchers-by-lang',
          FriendlyName: 'Avg Watchers by Lang',
          Query: {
            AggregatedScalarExpression:
              'AVG([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'percentage-watchers-by-lang',
          FriendlyName: '% Watchers by Lang',
          Query: {
            AggregatedScalarExpression:
              'PERCENTAGE([github_watchers], SUM([github_watchers], 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',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-sum-watchers-by-lang',
          Scope: {
            ColumnIds: [
              'sum-watchers-by-lang',
              'avg-watchers-by-lang',
              'percentage-watchers-by-lang',
              'percentage-issues-by-lang',
            ],
          },
          Style: {
            FontStyle: 'Italic',
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Grouped Rows

The values in Aggregation Calculated Columns can be used when summarising data for Grouped Rows.

AdapTable will ensure that if the Grid contains Aggregated Calculated Columns and Grouped Rows, the "aggFunc" summaries will display normally.

Deep Dive

A note on terminology

Calculated Columns: Aggregated with Row Grouping

Fork Fork

This demo contains 4 Calculated Columns that use Aggregation Expressions based on the Github Watchers Column
Like in the demo above all the Aggregated Scalar Expressions use GROUP_BY on the Language Column
We have also created a Layout which contains Row Grouping on the Language Column
The Layout also contains 4 AG Grid aggFunc summaries each connected to an AdapTable Aggregated Expression:
- SumByLang - uses a SUM AdapTable Aggregated Expression and a sum AG Grid aggFunc
- AvgByLang - uses a AVG AdapTable Aggregated Expression and a avg AG Grid aggFunc
- MinByLang - uses a MIN AdapTable Aggregated Expression and a min AG Grid aggFunc
- MaxByLang - uses a MAX AdapTable Aggregated Expression and a max AG Grid aggFunc
We have created an AG Grid Column Group to display the 4 Calculated Columns as a set

Try It Out

Update a cell in the Github Watchers Column and note how the 4 Calculated Columns update and so does the AG Grid agg summary

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Row Grouped Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
      PinnedToolbars: ['Layout'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Grouped',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'language',
            'github_watchers',
            'sum-watchers-by-lang',
            'avg-watchers-by-lang',
            'min-watchers-by-lang',
            'max-watchers-by-lang',
            'name',
          ],
        },
        {
          Name: 'Grouped',
          TableColumns: [
            'name',
            'github_watchers',
            'sum-watchers-by-lang',
            'avg-watchers-by-lang',
            'min-watchers-by-lang',
            'max-watchers-by-lang',
          ],
          RowGroupedColumns: ['language'],
          SuppressAggFuncInHeader: true,
          TableAggregationColumns: [
            {
              ColumnId: 'sum-watchers-by-lang',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'avg-watchers-by-lang',
              AggFunc: 'avg',
            },
            {
              ColumnId: 'min-watchers-by-lang',
              AggFunc: 'min',
            },
            {
              ColumnId: 'max-watchers-by-lang',
              AggFunc: 'max',
            },
          ],
          ColumnSizing: {
            name: {Width: 100},
            github_watchers: {Width: 150},
            'sum-watchers-by-lang': {Width: 100},
            'avg-watchers-by-lang': {Width: 100},
            'min-watchers-by-lang': {Width: 100},
            'max-watchers-by-lang': {Width: 100},
          },
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'sum-watchers-by-lang',
          FriendlyName: 'Sum',
          Query: {
            AggregatedScalarExpression:
              'SUM([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'avg-watchers-by-lang',
          FriendlyName: 'Avg',
          Query: {
            AggregatedScalarExpression:
              'AVG([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'min-watchers-by-lang',
          FriendlyName: 'Min',
          Query: {
            AggregatedScalarExpression:
              'MIN([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'max-watchers-by-lang',
          FriendlyName: 'Max',
          Query: {
            AggregatedScalarExpression:
              'MAX([github_watchers], GROUP_BY([language])) ',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-sum-watchers-by-lang',
          Scope: {
            ColumnIds: [
              'sum-watchers-by-lang',
              'avg-watchers-by-lang',
              'min-watchers-by-lang',
              'max-issues-by-lang',
            ],
          },
          Style: {
            FontStyle: 'Italic',
          },
        },
        {
          Name: 'formatColumn-avg-watchers-by-lang',
          Scope: {
            ColumnIds: ['avg-watchers-by-lang'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};