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

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',
            },
          },
        },
      ],
    },
  },
};

Cumulative Aggregations

(Recorded with AdapTable v13.2)

PreviousAggregated Calculated Columns NextQuantile Calculated Columns

Framework: