Referencing Calculated Columns

Summary

Once created a Calculated Column can be used and managed like any other Column in AG Grid
Calculated Columns can also be referenced in AG Grid Charts

Calculated Columns are treated like any other AdapTable (or AG Grid) Column.

Accordingly, a Calculated Column's Expression can reference other Calculated Columns in the Grid if required.

Caution

There is no limitation on how many Calculated Columns are chained - AdapTableQL will evaluate each in turn
However at some point performance might be impacted if you have a large chain with complicated calculations

Calculated Columns: Referencing

Fork Fork

This demo contains 3 Calculated Columns, one of which is evaluated based on the other 2
- Issues Open/Total Ratio - shows ratio between open and total number of issues
- PR Open/Total Ratio - shows ratio between open and total number of pull-requests
- Score - is calculated by computing the average value of the previous 2 ratio Calculated Columns (a higher score is better)

Try It Out

Change a value in Open Issues or Closed Issues and see how the Score Calculated Column also updates
Change Score to take the maximum value between 'Issues Open/Total Ratio' and 'PR Open/Total Ratio'

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Referencing Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'open_issues_count',
            'closed_issues_count',
            'open-total-issue-ratio',
            'open-total-pr-ratio',
            'score',
            'licence',
            'created_at',
            'github_stars',
          ],
          Name: 'Standard Layout',
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Query: {
            ScalarExpression:
              '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'PR Open/Total Ratio',
          ColumnId: 'open-total-pr-ratio',
          Query: {
            ScalarExpression:
              '[open_pr_count] / ([open_pr_count] + [closed_pr_count])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Score',
          ColumnId: 'score',
          Query: {
            ScalarExpression:
              'AVG([open-total-pr-ratio], [open-total-issue-ratio]) * 100',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatC olumn-open-total-issue-ratio',
          Scope: {
            ColumnIds: [
              'open-total-issue-ratio',
              'open-total-pr-ratio',
              'score',
            ],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Flashing Calculated Columns

Since Version 23.0 Calculated Columns can flash their cells when their contents change.

Note

Calculated Columns are not directly editable but they will update if a referenced column's value changes

It is possible for one Calculated Column to reference another Calculated Column and still flash when changed.

Calculated Columns: Flashing

Fork Fork

This example shows 2 Calculated Columns which flash when values in their referenced columns change:
- Total Stars + Watchers has an ANY_CHANGE() rule and flashes when one of its referenced Columns (Github Stars and Github Watchers) tick
- Stars Index (÷ 1000) references the first Calculated Column with a rule [stars-index] > 50, so the chained column flashes when its value changes (and the rule matches)

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Calculated Column Flashing',
  flashingCellOptions: {
    defaultFlashDuration: 600,
    defaultUpChangeStyle: {
      BackColor: '#86d586',
    },
    defaultDownChangeStyle: {
      BackColor: '#e18989',
    },
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'FlashingCell'],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          AutoSizeColumns: true,
          TableColumns: [
            'name',
            'github_stars',
            'github_watchers',
            'total-stars-watchers',
            'stars-index',
          ],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total Stars + Watchers',
          ColumnId: 'total-stars-watchers',
          Query: {
            ScalarExpression: '[github_stars] + [github_watchers]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Stars Index (÷ 1000)',
          ColumnId: 'stars-index',
          Query: {
            ScalarExpression: '[total-stars-watchers] / 1000',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FlashingCell: {
      FlashingCellDefinitions: [
        {
          Name: 'flash-total-stars-watchers',
          Scope: {
            ColumnIds: ['total-stars-watchers'],
          },
          Rule: {
            BooleanExpression: 'ANY_CHANGE()',
          },
        },
        {
          Name: 'flash-stars-index-high',
          Scope: {
            ColumnIds: ['stars-index'],
          },
          Rule: {
            BooleanExpression: '[stars-index] > 50',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'format-calc-columns',
          Scope: {
            ColumnIds: ['total-stars-watchers', 'stars-index'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 0,
            },
          },
        },
      ],
    },
  },
};

Calculated Columns and Alerts

Calculated Columns (also since Version 23.0) can trigger Alerts.

Note

This is most commonly a Data Change Alert with a Boolean Expression evaluated against the changed cell value

Similar to Flashing Cells its possible for one Calculated Column to reference another Calculated Column and still trigger the Alert.

Calculated Columns: Alerts

Fork Fork

This example shows 2 Calculated Columns which trigger Data Change Alerts when values in their referenced columns change:
- Total Stars + Watchers triggers an Alert (with a Notification) using the rule [total-stars-watchers] > 200000
- Stars Index (÷ 1000) references the first Calculated Column and a second Alert uses the rule [stars-index] > 150 on that chained column

Try It Out

Watch notifications appear when github_stars or github_watchers tick and their calculated values satisfy the Alert rules

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Calculated Column Alerts',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'Alert'],
      Tabs: [
        {
          Name: 'Toolbars',
          Toolbars: ['Alert'],
        },
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Alert'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          AutoSizeColumns: true,
          TableColumns: [
            'name',
            'github_stars',
            'github_watchers',
            'total-stars-watchers',
            'stars-index',
          ],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total Stars + Watchers',
          ColumnId: 'total-stars-watchers',
          Query: {
            ScalarExpression: '[github_stars] + [github_watchers]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Stars Index (÷ 1000)',
          ColumnId: 'stars-index',
          Query: {
            ScalarExpression: '[total-stars-watchers] / 1000',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    Alert: {
      AlertDefinitions: [
        {
          Name: 'High total stars + watchers',
          MessageType: 'Warning',
          MessageHeader: 'High engagement',
          MessageText: 'Combined stars and watchers exceeded 200,000',
          Scope: {
            ColumnIds: ['total-stars-watchers'],
          },
          Rule: {
            BooleanExpression: '[total-stars-watchers] > 200000',
          },
          AlertProperties: {
            DisplayNotification: true,
            HighlightCell: true,
          },
        },
        {
          Name: 'High stars index',
          MessageType: 'Info',
          MessageHeader: 'Stars index',
          MessageText: 'Chained calculated value is above 150',
          Scope: {
            ColumnIds: ['stars-index'],
          },
          Rule: {
            BooleanExpression: '[stars-index] > 150',
          },
          AlertProperties: {
            DisplayNotification: true,
            HighlightCell: true,
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'format-calc-columns',
          Scope: {
            ColumnIds: ['total-stars-watchers', 'stars-index'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 0,
            },
          },
        },
      ],
    },
  },
};

Deep Dive

Recalculating Dependent Calculated Columns

Calculated Columns in Charts

As "normal" Columns, Calculated Columns can also be referenced in Charts.

If a Chart contains a Calculated Column, then any changes to a Column which is referenced in the Calculated Columns's Expression, will cause the chart to re-render.

Charts Calculated Column

Fork Fork

This example demonstrates how Calculated Columns can be used in Charts
We create a Calculated Column called Total PRs which sums the Open PRs and Closed PRs columns
We then create a small Pie Chart which displays the names of the first 5 frameworks and the Total PRs
Any change to the underlying value of one of the Calculated Column Expression's constituent columns will cause the Chart to redraw

Try It Out

Set the Open PRs Column in the first row (for Vue) to be 22400 and see how the Chart changes

import {
  AdaptableButton,
  AdaptableOptions,
  CellUpdateRequest,
  CustomToolbarButtonContext,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Charts for Calculated Columns',
  dashboardOptions: {
    customToolbars: [
      {
        name: 'ButtonToolbar',
        title: 'Buttons',
        toolbarButtons: [
          {
            label: 'Update First Row',
            onClick: (
              button: AdaptableButton<CustomToolbarButtonContext>,
              context: CustomToolbarButtonContext
            ) => {
              const firstNode = context.adaptableApi.gridApi.getFirstRowNode();
              const primaryKeyValue = firstNode!.id;

              const open_pr_count = firstNode!.data['open_pr_count'];
              const cellUpdateRequest: CellUpdateRequest = {
                columnId: 'open_pr_count',
                newValue: open_pr_count + 100,
                primaryKeyValue: primaryKeyValue,
                rowNode: firstNode,
              };
              context.adaptableApi.gridApi.setCellValue(cellUpdateRequest);
            },
            buttonStyle: {
              tone: 'accent',
              variant: 'raised',
            },
          },
        ],
      },
    ],
  },
  chartingOptions: {
    chartContainers: [
      {
        name: 'Above Grid',
        element: '#demoOutputAbove',
      },
    ],
  },
  initialState: {
    Dashboard: {
      Tabs: [
        {Name: 'Charting', Toolbars: ['Charting', 'ButtonToolbar', 'Buttons']},
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Charting'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Query: {
            ScalarExpression: '[open_pr_count] + [closed_pr_count]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
          },
        },
      ],
    },
    Charting: {
      ChartDefinitions: [
        {
          Name: 'Top Frameworks',
          Model: {
            modelType: 'range',
            chartId: 'frameworks-pie',
            chartType: 'pie',
            chartThemeName: 'ag-vivid-dark',
            chartOptions: {
              pie: {
                background: {
                  visible: true,
                },
                padding: {
                  top: 20,
                  right: 20,
                  bottom: 20,
                  left: 20,
                },
                title: {
                  enabled: true,
                  text: 'Total PRs by Framework',
                },
                series: {
                  title: {
                    enabled: true,
                    text: '',
                  },
                  calloutLabel: {
                    enabled: true,
                  },
                  sectorLabel: {
                    enabled: true,
                  },
                },
              },
            },
            cellRange: {
              rowStartIndex: 0,
              rowEndIndex: 4,
              columns: ['name', 'total_pr_count'],
            },
            suppressChartRanges: false,
            unlinkChart: false,
          },
        },
      ],
    },

    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'total_pr_count',
            'open_pr_count',
            'closed_pr_count',
            'github_stars',
            'github_watchers',
            'language',
            'license',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'has_projects',
            'has_pages',
            'week_issue_change',
            'open_issues_count',
            'closed_issues_count',
          ],
          ColumnSorts: [{ColumnId: 'github_stars', SortOrder: 'Desc'}],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

PreviousQuantile Calculated Columns NextTechnical Reference