Calculation Columns
Calculation-type columns are powerful as they allow you to perform calculations and analysis on your dataset. They support dynamic updating of values based on changing inputs, facilitating budget calculations, progress tracking, and other essential project management functions.
Calculation-type columns may involve functions, numeric operations, logical operations, and text operations that operate on rows.
The configuration dialog of calculation columns is simple and consists of a calculation input where you will define the expression required:
A calculation expression may reference a number of things, including:
- Column names
- Strings and numbers
- Functions
- Logical operators
Column names
In a calculation, you can reference columns by name, surrounded by curly braces {}. To return the value of an existing column you can simply input the name of that column:
Configuring a simple calculation column .mp4
So for example, if you wanted a formula that calculated a total based on your Price and Quantity columns, that would look something like:
{Quantity} * {Price}
Chained expressions
You may also combine values, columns, and expressions together and that combination evaluates to a single value.
You can chain up several expressions together made up of logical operations mixing column names and numbers:
{Quantity}*({Price}*(1+({VAT rate}/100)))
You can also also combine functions with expressions using logical operations mixing column names and numbers in order to output strings. Taking our above example a step further, let's say we want to know when the total price of the units sold is over 1000:
IF(
{Quantity} * {Price} > 1000,
"This is over 1000",
"This is less than 1000"
)
Nested functions
You can also nest functions inside other functions to create more complex and sophisticated calculations. The example below will identify rows as overrunning if their "Status" column is set to in progress and their "Due Date" column is either set to today or any date before that.
IF(
AND(
{Status}="In Progress",
{Due Date} <= TODAY
),
"OVERRUNNING",
"ON TRACK"
)
Functions supported
You can find a list of the functions supported here. These consist of a variety of numeric, logical, data and time functions.