Standard Calculated Columns

Summary

Standard Calculated Columns show a value that is derived using other cell values in the same row
It can include as many Expression Functions and clauses as needed

The Expression provided by a Calculated Column is most commonly a Standard Expression.

This evaluates each row in turn and returns a single value of any type.

The evaluation for a Standard Calculated Column value is typically derived dynamically, using the values in other cells of the same row.

Note

There is no limit in the number of Expression Functions which can be included in a Calculated Column's Expression
The Expression will update in real time as values in referenced columns change

Find Out More

See Expressions to learn more about AdapTableQL, expression syntax, and the available functions
Consult the Standard Expressions topic for detailed information

Basic Expression

Most Calculated Column Expressions contain just one clause and uses the standard operators ('=', '>' etc.)

A very typical example is returning a mathematical evaluation on a few inputs.

Basic Calculated Columns

Fork Fork

This example has two Calculated Columns (using Standard Expressions):
- Subscribers Ratio - divides Github Stars by Github Watchers
- Total PRs sums Open PRs and Closed PRs

Expand to see the Calculated Column Definitions

The Calculated Columns are defined:

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

Try It Out

Create a new Calculated Column that calculates how many days passed since a row was last updated

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Basic Calculated Columns',
  columnOptions: {
    columnTypes: ['first', 'second'],
  },
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'subscribersRatio',
            'github_stars',
            'github_watchers',
            'total_pr_count',
            'open_pr_count',
            'closed_pr_count',
          ],
          Name: 'Standard Layout',
        },
      ],
    },
    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',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-subscribersRatio',
          Scope: {
            ColumnIds: ['subscribersRatio'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
      ],
    },
  },
};

Complex Expression

However it is possible to create more complex Calculated Columns.

These can either include multiple clauses or it can leverage some of the more powerful Expression Functions.

It is also possible to reference Custom Expression Functions in a Calculated Column Expression.

Hint

Any value can be returned in a Calculated Column (i.e. date or string or boolean) and not solely a number

Logic in Expressions

Calculated Column Expressions can also include logic functions if required.

These can take 2 forms:

ternary logic, ie. if X, then Y, else Z

[Comments] > 100 ? 'Big' : [Comments] > 50 ? 'Medium' : 'Small'

case statements, where different values are evaluated:

CASE WHEN [price] < 10 THEN 'low' WHEN [price] < 50 THEN 'medium' ELSE 'high' END

Calculated Columns: Complex Expressions

Fork Fork

This demo contains 5 Calculated Columns which each contain a Standard Expression (of varying complexity and with varying Data types):
- Years Old - calculates how many years passed since each framework repo was created - returns a Number
- Issues Open/Total Ratio - shows ratio between open and total number of issues - returns a Number
- PR Open/Total Ratio - shows ratio between open and total number of pull-requests - returns a Number
- Popularity - specifies the popularity taking into account the number of stars using ternary logic - returns a String
- Anniversary - adds 1 year to the Created Date column - returns a Date

Expand to see the Calculated Column Definitions and the AdapTableQL expressions used

CalculatedColumn: {
  CalculatedColumns: [
    {
      FriendlyName: 'Years Old',
      ColumnId: 'yearsOld',
      Query: {
        ScalarExpression: 'DIFF_YEARS(CURRENT_DAY(), [created_at])',
      },
      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',
      },
    },
    {
      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: 'Popularity',
      ColumnId: 'popularity',
      Query: {
        ScalarExpression:
          '[github_stars] > 100000 ? "Very Popular" : [github_stars] > 30000 ? "Popular" : "Trending"',
      },
      CalculatedColumnSettings: {
        DataType: 'text',
      },
    },
    {
      FriendlyName: 'Anniversary',
      ColumnId: 'anniversary',
      Query: {
        ScalarExpression: 'ADD_YEARS([created_at] , 1) ',
      },
      CalculatedColumnSettings: {
        DataType: 'date',
      },
    },
  ],
},

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Complex 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',
            'yearsOld',
            'created_at',
            'open-total-issue-ratio',
            'open-total-pr-ratio',
            'popularity',
            'anniversary',
            'github_stars',
          ],
          Name: 'Standard Layout',
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Years Old',
          ColumnId: 'yearsOld',
          Query: {
            ScalarExpression: 'DIFF_YEARS(CURRENT_DAY(), [created_at])',
          },
          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',
          },
        },
        {
          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: 'Popularity',
          ColumnId: 'popularity',
          Query: {
            ScalarExpression:
              '[github_stars] > 100000 ? "Very Popular" : [github_stars] > 30000 ? "Popular" : "Trending"',
          },
          CalculatedColumnSettings: {
            DataType: 'text',
          },
        },
        {
          FriendlyName: 'Anniversary',
          ColumnId: 'anniversary',
          Query: {
            ScalarExpression: 'ADD_YEARS([created_at] , 1) ',
          },
          CalculatedColumnSettings: {
            DataType: 'date',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-open-total-issue-ratio',
          Scope: {
            ColumnIds: ['open-total-issue-ratio', 'open-total-pr-ratio'],
          },

          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
            },
          },
        },
        {
          Name: 'formatColumn-anniversary',
          Scope: {
            ColumnIds: ['anniversary'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },
  },
};

PreviousCalculated Columns NextAggregated Calculated Columns

Framework: