AdapTable Version:

Framework:

Cumulative (Aggregation) Expressions

Summary

AdapTableQL performs cumulative aggregations by using the CUMUL and OVER functions

Cumulative Expressions, as the name suggests, perform cumulative Aggregations.

Hint

This is particularly useful when calculating Running Totals

In this use case, the Aggregation Operation is applied to each row in the set cumulatively.

Note

Cumulative Expressions are actually a special case of Aggregation Expression

Aggregation Operations

There are 5 Aggregation Operations available when using Cumulative Aggregations:

SUM
MIN
MAX
AVG
PERCENTAGE

Using `OVER` to set Order

The Aggregation Operation in a Cumulative Aggregation is applied in a specific order.

That order is specified in the Expression by the OVER keyword operator.

This takes the name of an Adaptable Column as its sole parameter.

Note

The OVER operator only works with Columns of type date or number

 // Derive cumulative sum of all PnL columns according to TradeDate order
 CUMUL(SUM([PnL]), OVER([TradeDate]))

 // Calculate cumulative maximum value of all PnL columns according to TradeDate order
 CUMUL(MAX([PnL]), OVER([TradeDate]))

Hint

There is no need physically to sort the Grid using the column specified by OVER
Indeed, the beauty of OVER is that ignores the current Grid Sort and uses the order in the given Column

AdapTableQL: Cumulative Aggregation

Fork Fork

This demo contains 2 Calculated Columns that use Cumulative Aggregation Scalar Expressions:
- Cumul Stars - displays the cumulative sum of stars count of all repos in the grid, aggregated over the Created date
- Max Technical Debt - shows the cumulative maxima of open issues of all repos in the grid, aggregated over the GitHub Stars count

Expand to see the Aggregated Cumulative Scalar Expressions

The 2 Calculated Columns are defined as follows:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'cumul-stars-count',
      FriendlyName: 'Cumul Stars',
      Query: {
        AggregatedScalarExpression: 'CUMUL( SUM([github_stars]), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'max-tech-debt-over-stars',
      FriendlyName: 'Maxi Technical Debt',
      Query: {
        AggregatedScalarExpression: 'CUMUL( MAX([open_issues_count]), OVER([github_stars]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
  ]
}

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Scalar Cumulative Expressions',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['CellSummary'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'created_at',
            'language',
            'github_stars',
            'open_issues_count',
            'cumul-stars-count',
            'max-tech-debt-over-stars',
          ],
          ColumnSorts: [{ColumnId: 'created_at', SortOrder: 'Asc'}],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'cumul-stars-count',
          FriendlyName: 'Cumul Stars',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( SUM([github_stars]), OVER([created_at]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Width: 200,
          },
        },
        {
          ColumnId: 'max-tech-debt-over-stars',
          FriendlyName: 'Max Technical Debt',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( MAX([open_issues_count]), OVER([github_stars]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Width: 200,
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
        {
          Name: 'formatColumn-number',
          Scope: {
            DataTypes: ['number'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              IntegerSeparator: ',',
            },
          },
        },
      ],
    },
  },
};

Grouped Cumulative Aggregation

Like with Standard Scalar Aggregations, Cumulative Aggregations can be grouped.

Aging this is accomplished by using the GROUP_BY function.

Caution

GROUP_BY is applied as a parameter to the Aggregation operator being used (e.g. SUM) and not to CUMUL

 // Cumulative sum of all PnL columns ordering by TradeDate, grouping by Currency
 CUMUL(SUM([PnL], GROUP_BY([currency])), OVER([TradeDate]))

Similar to Standard Scalar Aggregations, there is no limit on the number of Columns which can be grouped.

 // Cumulative sum of all PnL columns ordering by TradeDate, grouping by Currency and Country
 CUMUL(SUM([PnL], GROUP_BY([currency], [country])), OVER([TradeDate]))

AdapTableQL: Cumulative Aggregations Grouped

Fork Fork

This demo contains 2 Calculated Columns that use Cumulative Aggregation Scalar Expressions, one with grouping:
- Cumul Stars - displays the cumulative sum of stars count of all repos in the grid, aggregated over the Created date (same as in demo above)
- Cumul Lang Stars - displays the cumulative sum of stars count in the grid, but grouped by Language, and aggregated over the Created date

Expand to see the Aggregated Cumulative Scalar Expressions

The 2 Calculated Columns are defined as follows:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'cumul-stars-count',
      FriendlyName: 'Cumul Stars',
      Query: {
        AggregatedScalarExpression: 'CUMUL( SUM([github_stars]), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'cumul-language-stars-count',
      FriendlyName: 'Cumul Lang Stars',
      Query: {
        AggregatedScalarExpression:
          'CUMUL( SUM([github_stars], GROUP_BY([language])), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
  ]
}

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Aggregated Scalar Cumulative Expressions Grouped',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['CellSummary'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'created_at',
            'language',
            'github_stars',
            'open_issues_count',
            'cumul-stars-count',
            'cumul-language-stars-count',
            'max-tech-debt-over-stars',
          ],
          ColumnSorts: [
            {ColumnId: 'language', SortOrder: 'Asc'},
            {ColumnId: 'created_at', SortOrder: 'Asc'},
          ],
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'cumul-stars-count',
          FriendlyName: 'Cumul Stars',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( SUM([github_stars]), OVER([created_at]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Width: 200,
          },
        },
        {
          ColumnId: 'cumul-language-stars-count',
          FriendlyName: 'Cumul Lang Stars',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( SUM([github_stars], GROUP_BY([language])), OVER([created_at]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Width: 200,
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
        {
          Name: 'formatColumn-number',
          Scope: {
            DataTypes: ['number'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              IntegerSeparator: ',',
            },
          },
        },
      ],
    },
  },
};

PreviousAggregation Expressions NextQuantile Expressions

Framework:

Cumulative (Aggregation) Expressions

Summary

Hint

Note

Aggregation Operations

Using OVER to set Order

Note

Hint

Expand to see the Aggregated Cumulative Scalar Expressions

Grouped Cumulative Aggregation

Caution

Expand to see the Aggregated Cumulative Scalar Expressions

Using `OVER` to set Order