Advanced Calculations can be configured in a Calculation Field by entering an expression in the Calculation Expression Field. To learn how to create a Calculation Field, refer to the Creating a Field article.
The Calculation Expression can contain numerical values, mathematical operators, Field placeholders, and methods based on the EvalEx Library.
Example backend Field configuration:
Example front-end view:
Numerical values and operators:
A calculation can include numerical values and standard mathematical operators to compute a value. For example, a Calculation Field could be based on the expression (1+2)*3
in which case the result would always be 9.
Field Placeholders:
Field placeholders can be entered within an expression string such that other Fields within a Workflow are referenced. The placeholder string ‘%f’ is used to reference a Field. Once ‘%f’ is entered within an expression, a Field will appear allowing you to select the Field to map. You can add as many '%f' placeholders as you need and the corresponding number of input Fields will appear.
In the example below, if the calculation expression entered was
(1+%f)*3
The calculation would add 1 to the value of the Impact Field and multiply the result by 3.
In this example, the calculation expression is
%f * %f
There are 2 placeholders in the Calculation Expression and there are 2 Field Inputs respectively. The value of Impact is multiplied by the value of Likelihood to calculate this Field.
Calculation Field Deletion
Please note that you will receive a warning message when attempting to delete a Calculation Field. This message will require you to confirm whether or not you want to delete the Calculation Field and, along with it, all associated calculations. If you are sure, type the name of the Field within the text box and click the CONFIRM DELETION button.
Methods
Methods available are based on the EvalEx library. For example:
1. To take the absolute value of a Field you would enter the expression string:
abs(%f)
2. To take the average or sum of multiple fields, you would enter the following expression strings:
mean(%f, %f)
sum(%f, %f)
3. To round a calculation to a specific number of decimal places you would enter the expression string:
round(your calculation, # of decimals)
round(%f / %f, 2)*100
The expression above would round your calculation to 2 decimals
More information about the methods available can be found here.
4. To set a variable as the value of an input you would enter a string similar to this:
(variable = %f) * variable
This is equivalent to this, but allows you to only select the input once:
%f * %f
Conditional Operators
Use "if" statements to create calculations based on conditional logic. The basic syntax is below:
if( condition , exprIfTrue , exprIfFalse
)
Parameters:condition
- This is the expression that is used as a condition.exprIfTrue
- If the condition is true, then this expression is evaluated.exprIfFalse
- If the condition is false, then this expression is evaluated instead.
Note:
It is important that you use ==
to set a field equal to a specified value in Conditional If statements. In the EvalEx Library, the Operator =
is best suited for setting variables, not equivalency.
For example, if we wanted an expression that reads "If a field is equal to 5, then make it 1, else make it 0", it would be input like the following: if( %f==5 , 1 , 0 )
.
If you want to add in AND
/ OR
statements to the above expression it would look similar to: if(%f==5 && %f==2, 1, 0)
or if(%f==5 || %f==2, 1, 0)
. These expressions read as: "If Field Input 1 is equal to 5 AND Field Input 2 is equal to 2, then 1, else 0" and "If Field Input 1 is equal to 5 OR Field Input 2 is equal to 2, then 1, else 0".
Please find a list of Boolean Operators that are supported by EvalEx below.
Operator | Description |
= | Equals (Variable Setting) |
== | Equals (Equivalent to) |
!= | Not equals |
<> | Not equals |
< | Less than |
<= | Less than or equal to |
> | Greater than |
>= | Greater than or equal to |
&& | Boolean and |
|| | Boolean or |
Complex Use Case Example:
Let's say you have a Risk Score taking into consideration Impact and Likelihood, similar to the example above. However, you are not able to define clear quantitative thresholds. For example, Impact * Likelihood does not easily equate to 1-5 is considered "Low," 6-10 is "Medium," and so on.
Instead, the products of Impact and Likelihood are more in line with 1, 2, or 5 can be considered Low, 3 and 4 are considered "Low-Medium", and 6-10 are considered "Medium," and anything else should be considered High.
To accomplish this in a calculation field, you can use nested if statements:
1) The first step to take is to create a general calculation field multiplying Likelihood and Impact, as shown in the example at the beginning of this article. This will serve as your nested calculation variable, but will not be displayed on the form.
2) The next step is to create an additional calculation that will serve as the if statement, including the variable calculation, created in step one above.
We will also need to determine numerical values to be associated with the different thresholds of risk. We will make any score considered "Low" equal to 1, "Low-Medium", equal to 2, "Medium" equal to 3, and "High" equal to 10. This determination will be applied to Calculations Labels to be displayed on the form.
Based on the example mentioned, if the variable calculation equals 1, 2, or 5, then we should consider this "Low," which according to our numeric assignment italicized above, the scores will be equal to 1, this portion of the calculation is as follows:
if(%f == 1 || 2 || 5, 1
Then we would add in the additional values to account for the remaining scores to arrive at the final equation.
if(%f == 1 || 2 || 5, 1, if(%f == 3 || 4, 2, if(%f <= 10, 3, 10)))
Note: the %f in the equation above will always reference the same variable calculation that we created in step one. Alternatively, you may choose to define the variable at the onset of the equation, this would look like this:
if( (VAR = %f) == 1 || 2 || 5, 1, if(VAR == 3 || 4, 2,if(VAR <= 10, 3, 10)))