AdapTable Version:

Framework:

Calculated Columns

Summary

Calculated Columns are special columns which display a calculated value
Unlike regular columns, Calculated Columns do not display data from AG Grid's data source
Instead, the displayed values are derived (calculated) from other columns
The calculation takes the form of an AdapTableQL Expression
This is evaluated and kept in sync with the live data
The Expression always returns a single value and can be (depending on the complexity required):
- Standard - value returned is based on other cells in the row
- Aggregated - value returned is based on other rows in the Grid
- Cumulative - uses an Aggregation operation applied cumulatively to rows in specific order
- Quantile - value is derived from other rows using quantile aggregation
Once created a Calculated Column can be used and managed like any other Column in AG Grid

Calculated Columns are a special type of column which display a an evaluated value for each cell.

The Calculated Column definition contains an Expression which is evaluated dynamically by AdapTableQL.

The Expression is typically a Standard Expression (i.e. it evaluates the cell based on other columns in the row).

But there are more complicated Expressions types available for more advanced scenarios including Aggregated, Cumulative and Quantile Calculated Columns.

Hint

It is valid for a Calculated Column to display a static value, rather than an Expression, though this is a rare use case

Once created, Calculated Columns are treated as regular columns in AdapTable.

These columns do not exist in the underlying AG Grid data source but are still stored with Adaptable State.

Note

Calculated Columns - like Free Text Columns and Action Columns - are not "normal" data columns
Instead they are created dynamically by AdapTable each time the Application runs
By contrast Styled Columns are "normal" data columns, albeit ones which AdapTable specially renders

Calculated Column Value

The value displayed in the Calculated Column can be one of 4 types: string, number, date or boolean.

When creating a Calculated Column in the UI, AdapTable will infer the DataType from the Expression result.

Hint

The run-time user can override this inferred result if required
For Calculated Colunns provided via Initial Adaptable State, there is a mandatory DataType property

Deep Dive

How Calculated Columns Work in AdapTable

Calculated Column Expressions

Each Calculated Column definition contains a Query property, responsible for providing each cell value.

The Query wraps an Expression which is evaluated by AdapTableQL for each cell in the column.

Hint

Sometimes developers want to evaluate the Calculated Column themselves, rather letting AdapTable via do it
To do this, return 'false' for CalculatedColumn in the evaluateAdaptableQLExternally property in Expression Options

There are 4 types of Expressions available when using Calculated Columns:

Note

All are Scalar meaning the return value can be of any type

Calculation Type	How Calculated Value is Returned
Standard	Based solely on data in the current row
Aggregation	Derived from multiple, distinct rows (with optional grouping)
Cumulative	Aggregation Operation is applied to each row cumulatively in specific order
Quantile	Aggregated Values are placed into ordered buckets

Calculated Columns: Introducing

Fork Fork

This demo contains 3 (Numeric) Calculated Columns:
- Total PRs - sums the Open PRs and Closed PRs Columns
- Issues Open/Total Ratio - Divides OpenIssues by sum of OpenIssues and ClosedIssues
- Sum Pop by Lang - uses an Aggregation Expression to sum Github Stars grouped by Language

Expand to see the Calculated Column Definitions

CalculatedColumn: {
  CalculatedColumns: [
    {
      FriendlyName: 'Total PRs',
      ColumnId: 'total_pr_count',
      Query: {
        ScalarExpression: '[open_pr_count] + [closed_pr_count]',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      FriendlyName: 'Issues Open/Total Ratio',
      ColumnId: 'open-total-issue-ratio',
      Query: {
        ScalarExpression: '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
    {
      ColumnId: 'sum-popularity-by-lang',
      FriendlyName: 'Sum Pop by Lang',
      Query: {
        AggregatedScalarExpression: 'SUM([github_watchers], GROUP_BY([language]))',
      },
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
  ],
},

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Basic 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_pr_count',
            'closed_pr_count',
            'total_pr_count',
            'open_issues_count',
            'closed_issues_count',
            'open-total-issue-ratio',
            'github_watchers',
            'language',
            'sum-popularity-by-lang',
            'licence',
            'github_stars',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Query: {
            ScalarExpression: '[open_pr_count] + [closed_pr_count]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
          },
        },
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Query: {
            ScalarExpression:
              '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'sum-popularity-by-lang',
          FriendlyName: 'Sum Pop by Lang',
          Query: {
            AggregatedScalarExpression:
              'SUM([github_watchers], GROUP_BY([language]))',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Groupable: true,
          },
        },
      ],
    },
  },
};

Creating Calculated Columns

Calculated Columns, like most AdapTable Objects, can be created in 2 ways:

defined at design-time in Calculated Column Initial Adaptable State
created at run-time using the Calculated Column Wizard

Once created, Calculated Columns are stored in Adaptable State (like all Adaptable Objects).

Important

Only the Calculated Column Definition (e.g. Name, Expression, DataType etc) is stored in AdapTable State
AdapTable does not store any actual cell data

Find Out More

See Configuring Calculated Columns for instructions on providing Calculated Column Definitions

Formatting Calculated Columns

As stated above Calculated Columns, once created or defined, are treated like any other AdapTable Column.

This means, for example, that they can be used to trigger Alerts, or be included in Reports in Export.

It also allows Calculated Columns to be fully formatted with Styles, Display Formats and Conditions.

Hint

This allows rendering a Calculated Column as a Sparkline (typically by using the TO_ARRAY Expression Function)

Calculated Columns: Formatting

Fork Fork

This demo contains 5 Calculated Columns each of which has been been styled:
Three Columns have been given Column Formatting, some with Conditions:
- Subscribers Ratio - has 2 fraction digits and a suffix of '%'
- Total PRs - is right-aligned and italicised. Also has a Condition to show 'K' (with blue font) where > 1000
- Years Old - has a suffix of "years". Also has a Condition to show purple with white font where value is > 10
Two Columns have had a Styled Column applied:
- Big Stars - has a Percent Bar Style
- Stats - uses the TO_ARRAY function and then renders a Sparkline Column

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Formatting Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: [
        'CalculatedColumn',
        'FormatColumn',
        'StyledColumn',
        'SettingsPanel',
      ],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'subscribersRatio',
            'licence',
            'total_pr_count',
            'github_stars',
            'yearsOld',
            'bigStars',
            'language',
            'stats',
            'github_watchers',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Subscribers Ratio',
          ColumnId: 'subscribersRatio',
          Query: {
            ScalarExpression: '[github_stars] / [github_watchers]',
          },
          CalculatedColumnSettings: {
            ColumnTypes: ['first'],
            Filterable: true,
            Groupable: true,
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Query: {
            ScalarExpression: '[open_pr_count] + [closed_pr_count]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
          },
        },
        {
          FriendlyName: 'Years Old',
          ColumnId: 'yearsOld',
          Query: {
            ScalarExpression: 'DIFF_YEARS(CURRENT_DAY(), [created_at])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Big Stars',
          ColumnId: 'bigStars',
          Query: {
            ScalarExpression: '[github_watchers] * 5',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'stats',
          FriendlyName: 'Stats',
          Query: {
            ScalarExpression:
              'TO_ARRAY([closed_pr_count], [closed_pr_count], [open_issues_count], [closed_issues_count])',
          },
          CalculatedColumnSettings: {
            Resizable: true,
            DataType: 'numberArray',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-subscribersRatio',
          Scope: {
            ColumnIds: ['subscribersRatio'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
              Suffix: '%',
            },
          },
        },
        {
          Name: 'formatColumn-total_pr_count',
          Scope: {
            ColumnIds: ['total_pr_count'],
          },
          Style: {
            FontStyle: 'Italic',
            Alignment: 'Right',
          },
        },
        {
          Name: 'formatColumn-total_pr_count',
          Scope: {
            ColumnIds: ['total_pr_count'],
          },
          Rule: {
            Predicates: [
              {
                PredicateId: 'GreaterThan',
                Inputs: [1000],
              },
            ],
          },
          Style: {
            ForeColor: 'LightBlue',
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              Multiplier: 0.001,
              Suffix: 'K',
            },
          },
        },
        {
          Name: 'formatColumn-yearsOld',
          Scope: {
            ColumnIds: ['yearsOld'],
          },
          Rule: {
            Predicates: [
              {
                PredicateId: 'GreaterThan',
                Inputs: [10],
              },
            ],
          },
          Style: {
            BackColor: 'Purple',
            ForeColor: 'White',
          },
        },

        {
          Name: 'formatColumn-yearsOld',
          Scope: {
            ColumnIds: ['yearsOld'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              Suffix: ' years',
            },
          },
        },
      ],
    },
    StyledColumn: {
      StyledColumns: [
        {
          ColumnId: 'stats',
          SparklineStyle: {
            options: {
              type: 'area',
              fill: 'rgba(216, 204, 235, 0.3)',
              stroke: 'rgb(119,77,185)',
              axis: {
                type: 'category',
                stroke: 'rgb(204, 204, 235)',
              },
            },
          },
        },
        {
          ColumnId: 'bigStars',
          PercentBarStyle: {
            RangeValueType: 'Number',
            CellRanges: [
              {
                Min: 'Col-Min',
                Max: 'Col-Max',
                Color: 'green',
              },
            ],
            BackColor: '#d3d3d3',
          },
        },
      ],
    },
  },
};

Referencing Calculated Columns

Another beneficial consequence of Calculated Columns being treated like any other AdapTable Column is that any Calculated Column's Expression can reference other Calculated Columns in the Grid if required.

They can also be referenced in Charts; if a column referenced in the Calculated Column's Expression updates, the Chart will re-render accordingly.

Find Out More

See Referencing Calculated Columns for further details

Pivoting Calculated Columns

Because Calculated Columns are essentially regular columns, they can also be used in Pivot Layouts.

They can be referenced in 3 ways when pivoting:

Row Group Columns - AG Grid will display one row for each distinct value in the Calculated Column
Pivoted Columns - AG Grid automatically creates a column for each distinct value in the Calculated Column
Aggregation Columns - AG Grid will display the supplied aggFunc (typically sum but can be anything)

Pivoting Calculated Columns

Fork Fork

This Demo contains a Pivot Layout with 3 Calculated Columns each used as a different element in a Pivot Layout:
Total (Aggregated Column)
- adds the values of Github Starsand Github Watchers columns
- is used as an Aggregated Column in the Layout
- contains the sum aggregation
Popularity (Pivot Column)
- uses Ternary Logic to say whether the framework is popular (based on the value in Github Watchers)
- Used as a Pivot Column in the Layout
- Note: AG Grid automatically creates one dynamic, pivoted column for each of the 2 distinct Calculated Column values ('Popular', 'Unpopular')
Favourites (Row Grouped Column)
- uses Ternary Logic to say whether framework is a favourite (i.e. is one of "react", "angular", "vue", "stencil")
- This is used as a Row Group in the Layout
- Note: AG Grid automatically creates one grouped row for each of the 2 distinct Calculated Column values ('Main', 'Other')

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Pivoted Calculated Columns',
  initialState: {
    Dashboard: {
      Tabs: [{Name: 'Demo', Toolbars: ['Layout']}],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'Total',
          Query: {
            ScalarExpression: '[github_watchers] + [github_stars]',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Aggregatable: true,
            Filterable: false,
          },
        },
        {
          ColumnId: 'Popularity',
          Query: {
            ScalarExpression:
              '[github_watchers] > 500 ? "Popular" : "Unpopular" ',
          },
          CalculatedColumnSettings: {
            DataType: 'boolean',
            Pivotable: true,
            Filterable: false,
          },
        },
        {
          ColumnId: 'Favourites',
          Query: {
            ScalarExpression:
              '[name] IN ("react", "angular", "vue", "stencil") ? "Main" : "Other" ',
          },
          CalculatedColumnSettings: {
            DataType: 'boolean',
            Groupable: true,
            Filterable: false,
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Pivot Layout',
      Layouts: [
        {
          Name: 'Pivot Layout',
          PivotColumns: ['Popularity'],
          PivotGroupedColumns: ['Favourites'],
          SuppressAggFuncInHeader: true,
          PivotAggregationColumns: [
            {
              ColumnId: 'github_watchers',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'github_stars',
              AggFunc: 'sum',
            },
            {
              ColumnId: 'Total',
              AggFunc: 'sum',
            },
          ],
        },
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'github_stars',
            'github_watchers',
            'language',
            'Total',
            'Popularity',
            'Favourites',
            'updated_at',
            'description',
            'created_at',
            'has_wiki',
            'pushed_at',
            'open_issues_count',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Calculated Columns Performance

Calculated Columns are designed to be very performant.

However a very large Data Set, with multiple Calculated Columns that reference other Calculated Columns, might present a performance hit.

Note

This could be be particularly noticeable when sorting a column and AG Grid's Value Cache is unset

This is because a Calculated Column contains a Value Getter which is invoked each time a value is needed.

So when sorting a Column the same Value Getter will be called many times repeatedly.

Hint

In such scenarios, consider setting valueCache to true in AG Grid GridOptions
This will enable the Value Getter Cache (which comes with various Expiry options)

Using Calculated Columns

Run time users are able to create, edit, delete and share Calculated Columns via the Calculated Column section of the Settings Panel.

This displays a list of existing Calculated Columns with buttons to edit, share or delete each item.

Caution

There is no Suspend Button as Calculated Columns cannot be suspended
AdapTable will try to prevent you from deleting a Calculated Column which is referenced elsewhere

There is also an Add button to create new Calculated Columns using the Calculated Column Wizard.

Note

Newly created Calculated Columns are automatically added to the end of the Current Layout
Existing Calculated Columns include an Edit Calculated Column Menu Item in the Column Menu

UI Step by Step Guide

Using the Calculated Column Wizard

There are a few steps required when creating a Calculated Column:

Select Calculated Column Type

There are 4 types of Calculated Column which can be created (using the Expression Editor):

Standard - value is derived from other cells in Row
Aggregated - value is derived from aggregating multiple rows
Cumulative - value is derived cumulatively from other rows
Quantile - value is derived using quantile aggregations

Provide the Definition

The Calculated Column Definition step requires 3 properties to be provided:

Column Id - this is the value used to refer to the Calculated Column in the AdapTable UI.
Name (optional) - how the column will be referenced and what to appear in the Column's Header

Hint

This property will default to the ColumnId value, so only set this if you need it to be different

Header Tooltip (optional) - a Tooltip to display in the Column's Header

Write the Expression

The Expression Editor is a powerful UI tool designed to make writing Expressions easy, including:

a dropdown that provides all the Expression Functions available
context sensitive help
validation

Provide Column Settings

There are 2 settings that can be provided:

DataType - this is the DataType you require for the Column. Options are:
- String (the default)
- Number
- Date
- Boolean

Hint

AdapTable will infer this from the Expression set in the previous stage, so only override if required

Column Width - how wide the Calculated Column should be

Note

If left empty, the width will be set by AG Grid based on the other column definitions

Choose Column Properties (Optional)

There is a set properties that can be configured for the Calculated Column:

Filterable
Resizable
Groupable
Sortable
Pivotable
Aggregatable
Suppress Menu
Suppress Movable
Editable (always false)

Hint

All properties are false by default so check an option to turn on a feature

In addition any options that have been supplied in the columnTypes property of Column Options will also be displayed.

Click Finish

The new Calculated Column will be created, and will be added to the end of the Current Layout.

It will be given, where applicable, any properties defined in AG Grid DefaultColDef.

UI Entitlements

The UI Entitlements behaviour for Calculated Columns is as expected for Full and Hidden Access Levels.

The ReadOnly Entitlement behaviour is that Calculated Columns will display (and AdapTableQL will evaluate their values) but Users are not permitted to edit or delete them.

Calculated Column Changed Event

The Calculated Column Changed Event fires whenever anything changes in Calculated Column State, i.e. if a Calculated Column has been added, edited or deleted.

The Event includes details of the Action that triggered the Event and the associated Calculated Column.

Hint

This is particularly useful if you are evaluating Calculated Columns on the Server

FAQ

Which mathematical operators can we use to build the Expression? AdapTableQL provides a huge number of Expression Functions available to you when writing an Expression.

Can we add our own Expression Function and reference it when creating the Calculated Column? Yes, you can add your own Custom Expression Functions to AdapTableQL which will then be available for your users to use in their own Expressions.

Can I include more than one operator in an Expression? Yes, you can include as many operators as you wish in an Expression.

Can I use the Calculated Column in other Expressions? Yes, once the Calculated Column is created, then its treated the same as any other column and can be referenced other Modules (e.g. Column Formatting).

Can I edit the value a Calculated Column directly? No, Calculated Columns are readonly. You can edit the Expression but you cannot edit a cell in the column itself.

Can I create a Calculated Column that returns a string? Yes, your Calculated Column contains a Standard Expression so it can can return a number, string, date or boolean.

Can I change the name of a Calculated Column? You can change the FriendlyName of a Calculated Column but, once created, you cannot change the Id. The reason is that the columnId could already be being used in other objects like Layouts.

Can we refer to the Calculated Column in our AG Grid Column Definitions (e.g. to put it in a Column Group)? Yes. Ensure the colId matches the Calculated Column's columnID and add a Column Type of calculatedColumn.

Can I define the Calculated Column in AdapTable but provide my the evaluation myself using my server? Yes, return false for 'CalculatedColumn' in the evaluateAdaptableQLExternally property function in Expression Options. When this happens AdapTableQL will skip evaluation completely.

Can my Calculated Column reference other Calculated Columns? Yes it can - there are no limits on how many other Calculated Columns a Calculated Column can reference.

My Grid slows down when sorting referenced Calculated Columns? This happens because a Calculated Column contains a Value Getter which is invoked every time a value is needed. Enabling the Value Cache (by setting valueCache to true in AG Grid GridOptions) will speed things up considerably.

Can we flash Calculated Columns when they update? At the moment Calculated Columns will not flash. But we hope to allow this in a future version of AdapTable

AdapTable Resources

NextStandard Calculated Columns

Framework: