# Standard Calculated Columns

Canonical page: https://www.adaptabletools.com/docs/handbook-calculated-column-standard

- 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 used in a Calculated Column is most commonly a [Standard Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md).

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.

- There is no limit in the number of [Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) which can be included in a Calculated Column's Expression
- The Expression will update in real time as values in referenced columns change

- See [Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) to learn more about AdapTableQL, expression syntax, and the available functions
- Consult the [Standard Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md) topic for detailed information

### Defining a Standard Calculated Column

There are 5 properties that you need to provide when defining a Standard Calculated Column:

This `ColumnId` value is used to reference the Column in AdapTable State and in other objects (e.g. [Layouts](https://www.adaptabletools.com/docs/handbook-layouts/index.md)).

This value is used to refer to the Column in AdapTable UI.
It only needs to be provided if the `ColumnId` value is unsuitable.

This is the [Standard Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md) evaluated by [AdapTableQL](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) for each row in the grid.

It is the `Expression` string of the Calculated Column; because it contains no aggregation, AdapTable evaluates it per row.

The Data Type of the Calculated Column should be provided in `CalculatedColumnSettings`.

It is a [Cell Data Type](https://www.adaptabletools.com/docs/dev-guide-aggrid-cell-data-types/index.md) value and the object's only mandatory property. Common values are `number`, `text` or `date`.

Additional Settings for the Calculated Column (other than mandatory `DataType`) can be provided, including:

- `Filterable`
- `Pivotable`
- `Sortable`
- `Groupable`
- `CellDataType` - an optional custom [AG Grid Cell Data Type](https://www.adaptabletools.com/docs/dev-guide-aggrid-cell-data-types/index.md) used to render the cell (here `total_pr_count` keeps a `number` `DataType` but displays using a registered `currency` type)

```tsx [[1,4, "ColumnId"],[1,13, "ColumnId"],[2,5, "FriendlyName"],[2,14, "FriendlyName"],[3,6, "Expression"],[3,15, "Expression"],[4,8, "DataType"],[4,17, "DataType"],[5,9, "CellDataType"],[6,18, "Sortable"]]
CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'total_pr_count',
      FriendlyName: 'Total PRs',
      Expression: '[open_pr_count] + [closed_pr_count]',
      CalculatedColumnSettings: {
        DataType: 'number',
        CellDataType: 'currency',
      },
    },
    {
      ColumnId: 'popularity',
      FriendlyName: 'Popularity',
      Expression: '[github_stars] > 100000 ? "Very Popular" : [github_stars] > 30000 ? "Popular" : "Trending"',
      CalculatedColumnSettings: {
        DataType: 'text',
        Sortable: true,
      },
    },
  ],
},
```

## 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.

**Example: Basic Calculated Columns**

Calculated Columns: Basic Standard

- This example has two Calculated Columns (using [Standard Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md)):
  - `Subscribers Ratio` - divides Github Stars by Github Watchers
  - `Total PRs` sums Open PRs and Closed PRs
  

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

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Basic Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    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',
          Expression: '[github_stars] / [github_watchers]',
          CalculatedColumnSettings: {
            Filterable: true,
            Groupable: true,
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Expression: '[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](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md) in a Calculated Column Expression.

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](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-logic/index.md) if required.

These can take 2 forms:

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

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

- case statements, where different values are evaluated:

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

**Example: Calculated Columns: Complex Expressions**

Calculated Columns: Complex Expressions

- 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*

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Complex Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    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',
          Expression: 'DIFF_YEARS(CURRENT_DAY(), [created_at])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Expression: '[open_issues_count] / ([open_issues_count] +[closed_issues_count])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'PR Open/Total Ratio',
          ColumnId: 'open-total-pr-ratio',
          Expression: '[open_pr_count] / ([open_pr_count] + [closed_pr_count])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          FriendlyName: 'Popularity',
          ColumnId: 'popularity',
          Expression: '[github_stars] > 100000 ? "Very Popular" : [github_stars] > 30000 ? "Popular" : "Trending"',
          CalculatedColumnSettings: {
            DataType: 'text',
          },
        },
        {
          FriendlyName: 'Anniversary',
          ColumnId: 'anniversary',
          Expression: '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',
            },
          },
        },
      ],
    },
  },
};
```
