Add a read-only column whose values are computed automatically from a formula that references other columns in the same table.
Required permission: Edit Table Data. This permission is available to Finance Users and Admins by default.
Add a calculated column
- Open the table, enter Edit Mode
- Select + Column.
- Select the Calculation data type.
- Enter a Column Name.
- Enter your formula in the formula editor.
- Select Save. Cube validates the formula before saving.
The column's values compute once you confirm the formula with the Green Check, and update on the next page load or when you select Save.
Formula syntax
Calculated columns support the following:
- Column references: wrap a column name in brackets, for example [Annual Salary].
- Arithmetic operators: +, -, *, /.
- Comparison operators: =, !=, >, <, >=, <=.
- IF/THEN/ELSE conditionals: IF([condition], [value_if_true], [value_if_false]).
- String comparisons in conditionals: for example IF([Level] = "L5", [Salary] * 0.15, [Salary] * 0.10).
- Nested parentheses for order of operations.
- Empty string checks: for example IF([End Date] = "", "Active", "Terminated").
Examples
Monthly salary from an annual figure
Formula: [Annual Salary] / 12
For an employee with a $180,000 annual salary, the column shows $15,000.
Bonus tiered by level
Formula: IF([Level] = "L5", [Annual Salary] * 0.15, [Annual Salary] * 0.10)
An L5 employee at $180,000 shows a $27,000 bonus. An L3 employee at $95,000 shows $9,500.
Employment status from a date
Formula: IF([End Date] = "", "Active", "Terminated")
Employees without an End Date show Active. Employees with an End Date show Terminated.
Rules
Note: Calculated columns are read-only. You cannot edit individual cells in a calculated column. To change a value, edit the source column it references.
- Calculated columns can reference any non-calculated column in the same table.
- Calculated columns cannot reference other calculated columns.
- If a formula references an empty or null value: arithmetic with null returns null, and a string comparison treats null as an empty string.
- Cube validates formula syntax when you save. Invalid syntax or references to a column that does not exist are rejected with an error message.