Cumulative Calculated Columns

Summary

Cumulative Calculated Columns perform cumulative aggregations
The aggregation operation is applied to each row cumulatively in a specific, given order

Calculated Columns are able to display Cumulative Expressions.

These are an advanced form of Aggregation Expression.

They are similar in that the values derive from multiple rows but with one fundamental difference: they perform that perform cumulative aggregations.

This means that the aggregation operation (e.g., SUM, MIN, MAX) is applied to each row cumulatively in a specific, given order.

Hint

This is particularly useful, for example, when we want to calculate a Running Total of a column

Developer Guide

Defining a Cumulative Calculated Column

There are 5 properties that you need to provide when defining a Cumulative Calculated Column:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'cumulated-stars-count',
      FriendlyName: 'Cumulated Stars Count',
      Query: {
        AggregatedScalarExpression:
          'CUMUL( SUM([github_stars]), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Filterable: true,
      },
    },
    {
      ColumnId: 'max-tech-debt-over-stars',
      FriendlyName: 'Maximum Technical Debt',
      Query: {
        AggregatedScalarExpression:
          'CUMUL( MAX([open_issues_count]), OVER([github_stars]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
        Sortable: true,
      },
    },
  ],
},

Id for the Calculated Column

This ColumnId value is used to reference the Column in AdapTable State and in other objects (e.g. Layouts).

Column Friendly Name (if required)

This value is used to refer to the Column in AdapTable UI. It only needs to be provided if the ColumnId value is unsuitable.

The Cumulative Expression

This is a Cumulative Expression evaluated by AdapTableQL cumulatively across the grid in a specified order.

It is wrapped inside a Query property under the AggregatedScalarExpression key and uses the CUMUL function with an inner aggregation (SUM, MIN, MAX, etc.) and an OVER clause that sets the ordering.

Data Type

The Data Type of the Calculated Column should be provided in CalculatedColumnSettings.

It is a Cell Data Type value and the object's only mandatory property. Common values are number, text or date.

Calculated Column Settings (Optional)

Additional Settings for the Calculated Column (other than mandatory DataType) can be provided, including:

Filterable
Pivotable
Sortable
Groupable

Calculated Columns: Cumulative

Fork Fork

This demo contains 2 Calculated Columns that use Cumulative Aggregation Scalar Expressions:
- Cumulated Stars Count - displays the cumulative sum of stars count of all repos in the grid, aggregated over the Created date
- Maximum Technical Debt - shows the cumulative maxima of open issues of all repos in the grid, aggregated over the GitHub Stars count
The Calculated Columns are formatted using Column Formatting.

Expand to see the Cumulative Aggregation Scalar Expressions

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'cumulated-stars-count',
      FriendlyName: 'Cumulated Stars Count',
      Query: {
        AggregatedScalarExpression:
          'CUMUL( SUM([github_stars]), OVER([created_at]) )',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'max-tech-debt-over-stars',
      FriendlyName: 'Maximum 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: 'Cumulative Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'created_at',
            'github_stars',
            'open_issues_count',
            'cumulated-stars-count',
            'max-tech-debt-over-stars',
          ],
          ColumnSorts: [{ColumnId: 'created_at', SortOrder: 'Asc'}],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'cumulated-stars-count',
          FriendlyName: 'Cumulated Stars Count',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( SUM([github_stars]), OVER([created_at]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'max-tech-debt-over-stars',
          FriendlyName: 'Maximum Technical Debt',
          Query: {
            AggregatedScalarExpression:
              'CUMUL( MAX([open_issues_count]), OVER([github_stars]) )',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },
  },
};

PreviousAggregated Calculated Columns NextQuantile Calculated Columns