Creating Advanced Calculations

Learn how to create advanced expressions in a Calculation Field 

Advanced Calculations can be configured in a Calculation Field by entering an expression in the "Calculation Expression" section of the 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.

The following image shows a example of what the backend configuration of a Calculation Field looks like:

The following image shows what a Calculation Field looks like to an end user. 

Numerical values and operators

A Calculation Expression 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 the values of other Fields in the Workflow are used for the calculation. The placeholder string %f is used to reference a Field. Once %f is entered within an expression, an "Input Field" drop-down will appear, allowing you to select the appropriate Field for input. You can add as many %f placeholders as you need and the corresponding number of "Input Field" drop-downs will appear.

Example 1

In the example below, the Calculation Expression is (1+%f)*3 .

The calculation will add 1 to the value of the Field selected (Inherent Impact in this example) and multiply the result by 3. Since there is only one Field placeholder in the expression, there is only one Field Input.

Example 2

In the example below, the calculation expression is %f * %f .

Since there are two Field placeholders, there are two Field Inputs. These are multiplied for this calculation. In this example, the two inputs Inherent Impact and Inherent Likelihood are multiplied to calculate the Inherent Risk Score. 

Configuring Calculations Specific to a Step

Calculations can be configured to only consider records in a specific step. When you select your field inputs, you will see a checkbox below each field input: “Only consider records in a specific step.” If you check this box, a Step dropdown menu will appear. The calculation will only take the field input value into account for records in the step selected in the dropdown menu.

For example, if you only want the Inherent Risk Score to be calculated for Risk records which have been fully evaluated, you would select the Risk Repository step. Risk records in any other step would be treated as blank in the calculation.

In this example, if you select the Risk Repository step for each field input and select “Do nothing (N/A)” for Blank Field Handling, the Inherent Risk Score will display “N/A” for all Risk records which are not in the Risk Repository step.

Calculation Field Deletion

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 all associated calculations. If you are sure, type the name of the Field within the text box and click Confirm Deletion.

Methods

Methods available are based on the EvalEx library. For example:

1. To take the absolute value of a Field enter:

abs(%f) 

2. To take the average or sum of multiple fields, you would enter the following expression strings respectively: 

mean(%f, %f)

sum(%f, %f)

3. To round a calculation to a specific number of decimal places you would enter an expression string with the following syntax: 

round(your calculation, # of decimals)

The expression below would round your calculation to 2 decimals. More information about the methods available can be found here.
round(%f / %f, 2)*100  

4. To set a variable as the value of an input you would enter a string similar to this:

(variable = %f) * variable

The expression above is equivalent to %f * %f, but the expression above allows you to select an input only once. 

Conditional Operators
You can use "if" statements to create calculations based on conditional logic. The basic syntax is as follows: 
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 the syntax would be: 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.

Complex Use Case Example:

Let's say you have a Risk Score taking into consideration Inherent Impact and Inherent Likelihood, as in the examples used above. However, suppose 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)))

For a more in depth look at advanced calculations, refer to the our ‘Creating Advanced Calculations- Expanded’ article, for more helpful tips and useful examples!