OVERVIEW
The Calculated Fields feature allows you to assign a value to a field using statistical and arithmetic calculations based on an expression that uses values of other fields within the same item, the parent item, or children.
This guide will walk you through setting up a field to calculate its value using a specific formula.
This feature is available from version 5.2.5 as part of the Orcanos Professional plan
Setting up a Calculated Field
A calculation expression can be set in the Admin > Fields Layout only for custom fields and in the current Work Item layout context.
The same custom field can have one calculation expression in Work Item A and another in Work Item B.
Once a calculation is set for a field, the calculated value will be set in this field when saving an item or clicking the Refresh Calculation button.
Supported Field Types
Calculations are supported for custom fields of type: numeric, single-line, or multiline text.
The “Enable calculation” option will not be visible for fields other than those mentioned above.
Simple Walkthrough
Expression Syntax
A Calculation Expression is formed by a function or an arithmetic expression over a field’s values in a given context.
The Context and the Field names are enclosed in square brackets and asterisks without spaces.
Function([*Context.Field Name*]
For example:
AVERAGE([*Children.Effort Estimation*])
There are three available contexts for fetching the field values:
- Current Item: [*Me.Field Name*]
This will fetch the field value from the work item itself. - Parent Item: [*Parent.Field Name*]
This will fetch the field value from the parent item in the project tree. - Children items: [*Children.Field Name*]
This will fetch an array of field values from all the item’s children in the project tree.
Supported Functions and Operators
Here is a list of popular mathematical functions and operators, along with some examples.
Some functions, like Average, operate on an array of numbers and are commonly used in the context of statistics.
Please see the full list of supported logical and mathematical functions below.
- Supported Operators are + (add), – (substract), * (multiply), / (devide), ^ (power)
Examples:
([*Me.Field Name 1*] + [*Me.Field Name 2*]) / 2
([*Parent.Field Name 3*]) ^2
- SUM – Return the sum of numbers.
Example: SUM([*Children.Field Name*]) - AVERAGE – Return average of numbers.
Example: AVERAGE([*Children.Field Name*]) - MIN – Return the smallest value from the numbers supplied.
Example: MIN([*Children.Field Name*]) - MAX – Return the biggest value from the numbers supplied.
Example: MAX([*Children.Field Name*]) - COUNT—Count the number of values in the array. If a field does not exist or has no value (the field is empty) in a child item, it will not be counted.
Example: COUNT([*Children.Field Name*]) - Standard Deviation
STDEV – Returns the standard deviation from a sample of data.
Example: STDEV(children.[*Field Name*])
STDEVP – Returns the standard deviation of an entire population.
Example: STDEVP(children.[*Field Name*])
Precision considerations
The system will display calculated fields with precision up to four decimal places.
To round up the result, apply the ROUND function over the expression.
Example: ROUND(AVERAGE([*Children.Effort Estimation*]), 2)
In this example, the result was rounded up to two decimal places.
Using a Calculated Field
Once a Calculated Field is set for a Work Item, it will appear in the item overview as not editable (grayed out) with a formula icon indicating that its value is calculated by a formula.
The Calculated Field value will be recalculated with any save action on the Work Item, or by clicking the Refresh Calculation button.
** Attention: updating a value in one of the children or the parent will not automatically trigger the recalculation of this item.
Calculation Order
Calculations are done by order of the calculated fields position in the form. on a section with several calculated fields ordered in two columns the calculation is done in this order: the first top field on the left column and the last calculation will be on the second column, last field at the bottom of the column.
Tooltip of a Calculated field
The tooltip of a Calculated field shows the mathematical expression used in calculating the value of the field. It is present at the end of the field box with 
symbol, upon mouse hover the mathematical expression can be seen.
Error Handling
In case a calculation was failed an error message will be displayed on the field.
This error gives a hint of what went wrong to fix the calculation.
Here are some examples of possible errors:
- If the specified field in the Calculation Expression is not found in the given context, the following error message will be displayed.
- In the event that the calculation attempted to divide by zero due to retrieving a zero value from a field in the denominator of an expression.
- The following error may occur if the selected field value type does not match the calculated field type. For example, fetching a text field value into a numeric calculation.
List of supported operators, constants and functions
Supported Operators
| Operator | Description |
| + | Additive operator / Unary plus / Concatenate string / Datetime addition |
| – | Subtraction operator / Unary minus / Datetime subtraction |
| * | Multiplication operator, can be omitted in front of an open bracket |
| / | Division operator |
| % | Remainder operator (Modulo) |
| ^ | Power operator |
Supported logical and math functions
| Function | Description |
| AND(logical1, [logical2], …) | Determine if all conditions are TRUE |
| OR(logical1, [logical2], …) | Determine if any conditions in a test are TRUE |
| NOT(logical) | To confirm one value is not equal to another |
| XOR(logical1, [logical2], …) | Exclusive OR function |
| SUM(number1, [number2],…) | Return sum of numbers supplied |
| AVERAGE(number1, [number2],…) | Return average of numbers supplied |
| MIN(number1, [number2],…) | Return the smallest value from the numbers supplied |
| MAX(number1, [number2],…) | Return the biggest value from the numbers supplied |
| MOD(number, divisor) | Get remainder of two given numbers after division operator. |
| ROUND(number, num_digits) | Returns the rounded approximation of given number using half-even rounding mode
( you can change to another rounding mode) |
| FLOOR(number, significance) | Rounds a given number towards zero to the nearest multiple of a specified significance |
| CEILING(number, significance) | Rounds a given number away from zero, to the nearest multiple of a given number |
| POWER(number, power) | Returns the result of a number raised to a given power |
| RAND() | Produces a random number between 0 and 1 |
| SIN(number) | Returns the trigonometric sine of the angle given in radians |
| SINH(number) | Returns the hyperbolic sine of a number |
| ASIN(number) | Returns the arc sine of an angle, in the range of -pi/2 through pi/2 |
| COS(number) | Returns the trigonometric cos of the angle given in radians |
| COSH(number) | Returns the hyperbolic cos of a number |
| ACOS(number) | Returns the arc cosine of an angle, in the range of 0.0 through pi |
| TAN(number) | Returns the tangent of the angle given in radians |
| TANH(number) | Returns the hyperbolic tangent of a number |
| ATAN(number) | Returns the arc tangent of an angle given in radians |
| ATAN2(x_number, y_number) | Returns the arctangent from x- and y-coordinates |
| COT(number) | Returns the cotangent of an angle given in radians. |
| COTH(number) | Returns the hyperbolic cotangent of a number |
| SQRT(number) | Returns the correctly rounded positive square root of given number |
| LN(number) | Returns the natural logarithm (base e) of given number |
| LOG10(number) | Returns the logarithm (base 10) of given number |
| EXP(number) | Returns e raised to the power of given number |
| ABS(number) | Returns the absolute value of given number |
| FACT(number) | Returns the factorial of a given number |
| SEC(number) | Returns the secant of an angle given in radians |
| CSC(number) | Returns the cosecant of an angle given in radians |
| PI() | Return value of Pi |
| RADIANS(degrees) | Convert degrees to radians |
| DEGREES(radians) | Convert radians to degrees |
| INT(number) | Returns the Integer value of given number |
Supported Constants
| Constant | Description |
| e | The value of e |
| PI | The value of PI |
| NULL | The null value |
Additional Math functions
| Constant | Description |
| Count | Count the number of values in the array |
| STDEV | Returns the standard deviation from a sample of data |
| STDEVP | Returns the standard deviation of an entire population |