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

Referenced Calculated Columns

(Recorded with AdapTable v16.0)

Calculated Columns in Charts

Calculated Columns, once created, are normal columns and can therefore 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 NextConfiguring Calculated Columns

Framework: