Calculated Column

The Calculated Column Function enables the creation of special 'user-defined' columns which contain a custom 'Expression' which is evaluated at design-time per row.

tip

Calculated Columns can either be provided at design-time (through Calculated Column Predefined Config) or at run-time through the UI (if Entitlements allow).

Unlike 'regular' columns, Calculated Columns do not display data from the underlying grid's Data Source.

Calculated Columns can either be static (i.e. show a single value) or dynamic (i.e. based on other columns in the row).

important

Once created a Calculated Column is treated like any other Column in the Grid

caution

Only the Calculated Column Name and Expression are stored in AdapTable State and not any cell data

There are numerous potential uses for Calculated Columns including showing Historical Data, and allowing you to do 'what if' or scenario analysis by copying some columns and see what happens if you change their values.

Calculated Column Expression

Calculated Columns include an Expression that users provide in the Expression Editor.

The Expression can contain any number of functions and references to other columns in the grid as needed.

rmation

Up to Version 7 of AdapTable the Expression used an extermal library (Math.js) but this dependency has been removed and Expressions are now created and evaluated using an internal parser.

The Expression in the Calculated Column updates in real time as values in the columns to which it refers change.

For more information on the Expression including syntax, examples and a list of the available functions see Adaptable Expression.

And see some example Expressions in the Calculated Column Demo.

Calculated Column Settings

When creating a Calculated Column there are a number of properties you can provide so that the column will operate according to your requirements. These include:

  • Column Type

    tip

    When creating a Calculated Column in the UI, AdapTable will infer this from the Expression and your data but you can override this if the assumption is incorrect

  • Width

    note

    If left empty, the width will be set by the underlying grid based on whatever other properties are set

  • Filterable

  • Resizable

  • Groupable

  • Sortable

  • Pivotable

  • Aggregatable

UI Elements

Calculated Column includes the following UI Elements:

  • Popup - Shows a list of existing Calculated Columns with Edit and Delete buttons. Plus an Add button to start the Calculated Column Wizard, that contains a series of steps facilitating the creation and editing of Calculated Columns.

  • Column Menu - Edit Calculated Column Menu Item opens the Calculated Column wizard (only visible if that column is a Calculated Column).

Entitlements

Calculated Column Entitlement Rules:

  • Full: Everything is available to the User

  • Hidden: Everything is hidden from the User

  • ReadOnly: User can see Calculated Columns defined in Predefined Config but not add, edit or delete them.

Calculated Column State

Calculated Column Predefined Config contains a collection of Calculated Columns.

Calculated Column Object

Each Calculated Column is defined as follows:

PropertyDescription
CalculatedColumnSettingsAddtional optional properties for Column (e.g. filterable, resizable)
ColumnExpressionExpression which AdapTable parser uses to evaluate Column' display value
ColumnIdName of Calculated Column
FriendlyNameName to be used in Column Header; if blank ColumnId is used

Calculated Column Settings

Calculated Column Settings describes the created column and contains these properties:

PropertyDescriptionDefault
AggregatableWhether Column can be used in an aggregation when groupingtrue
DataTypeExpression's return value DataType; inferred by AdapTable but setable by User
FilterableWhether Column is filterabletrue
GroupableWhether Column can be groupedtrue
PivotableWhether Column can be used when grid is in pivot modetrue
ResizableWhether Column can be resized (by dragging column header edges)true
ShowToolTipShow underlying Expression as ToolTip when hovering over a cellfalse
SortableWhether Column is sortabletrue
WidthPreferred width (in pixels) for Column; if unset is calculated dynamically by underlying Grid

Calculated Column Config Example

const demoConfig: PredefinedConfig = {
CalculatedColumn: {
CalculatedColumns: [
{
ColumnExpression: '[ItemCost] / [ItemCount]',
ColumnId: 'AvgCost',
FriendlyName: 'Avg Item Cost',
},
{
ColumnExpression: '([ItemCost] * [ItemCount])- [PackageCost]',
ColumnId: 'Profit',
FriendlyName: 'profit',
},
{
ColumnExpression: '[ItemCost] > 100 ? "High" : [ItemCost] > 50 ? "Medium": "Low"',
ColumnId: 'Comment',
FriendlyName: 'Comment',
CalculatedColumnSettings: {
DataType: 'String',
Filterable: true,
Groupable: true,
Sortable: true,
},
},
{
ColumnExpression: 'max([ItemCost], [OrderCost], ([PackageCost]*10))',
ColumnId: 'HighCost',
FriendlyName: 'Highest Cost',
},
{
ColumnExpression: "[ShippedDate] > ADD_DAYS([OrderDate] , 21) ? 'Late' : 'On time'",
ColumnId: 'ShipDelay',
FriendlyName: 'Ship Delay',
},
],
},
} as PredefinedConfig;

Calculated Column API

Calculated Column API contains a number of Calculated Column related functions:

MethodDescription
addCalculatedColumn(calcColumn)Adds new Calculated Column
deleteCalculatedColumn(calcColumn)Removes Calculated Column from State
editCalculatedColumn(calcColumn)Edits given Calculated Column
editCalculatedColumnExpression(calcColumn, columnExpression)Replaces Expression in given Calculated Column with new value
getAllCalculatedColumn()Retrieves all Calculated Columns in Adaptable State
getCalculatedColumnState()Retrieves Calculated Column section from Adaptable State
showCalculatedColumnPopup()Opens Calculated Column popup screen

FAQ

Which mathematical operators can we use to build the Expression?

There are a huge number of functions currently available when writing an Expression and more are being added all the time.

Can we add our own function and then refrence it in the Expression?

Not at the moment, but that funcionality will be coming soon.

Its quite cumbersome to have to write the Expression by hand. Is there a GUI option?

Yes, this was introduced in Version 7.

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 Queries?

Yes, once the Calculated Column is created, then its treated the same as any other column and can be referenced in Queries and other Functions (e.g. Conditional Style or Format Column).

Can I edit the value a Calculated Column directly?

No, a Calculated Column is readonly. You can edit the Expression but you cannot edit one of the cells in the column itself.

Can I create a Calculated Column that returns a string?

Yes, your Calculated Column can return a number, string, date or boolean. AdapTable will work out the datatype of the Calculated Column automatically, so that the created Column is treated like any other Column in queries and Expressions.

Can I change the name of a Calculated Column?

You can change the FriendlyName of a Calculated Column, but you cannot change the Id (as this could be used in other objects like Layouts.

warning

AdapTable will prevent you from deleting a Calculated Column that is referenced elsewhere.

More Information