This article provides a list of common formulas. Note that these formulas contain variables and calculations that may not be applicable to your organization. For more detailed information on formulas, see the following articles:
- Formulas Overview
- Variables, Operators & Functions
- Time Functions
- Null Values in Formulas
- Add Formulas to an Object Type
Examples
When reviewing the examples below, note that:
- Variables represent numeric and date fields, select lists, workflow states, or other formulas; and
- Functions require data from multiple objects. As such, only Relationship and Reference variable types can be used with a function.
NAME | FORMULA | VARIABLES | OPERATORS | FUNCTIONS | RESULT |
| Average Impact | MEAN(IMPACT) | IMPACT (Inherent Impact) | Mean | Calculates the mean value of Inherent Impact from objects in the Risks relationship, divided by the number of times the variable appears on the objects. | |
| Control Effectiveness | CONTROLEFF | CONTROLEFF (Control Effectiveness) | Provides a count of the value from the Control Effectiveness field. Note that if a count of values from multiple objects is required, a relationship or reference variable type used with the sum function is required. | ||
| Design Effective | DE==1?1:0 | DE (Design Effectiveness) | == (equal to), ? : (conditional expression) | Checks if Design Effectiveness equals 1. If yes, the formula outputs 1, otherwise, it outputs 0. | |
| Indicator Status | (CURRENT-TARGET)*DIRECTION | CURRENT(Current Value), TARGET (Target Value), DIRECTION (Desired Direction) | ( ) (grouping), - (minus), * (multiply) | Subtracts the value of Target Value from Current Value, then multiplies the total by the value of Desired Direction. | |
| Inherent Risk Score | IN_IMPACT*IN_LIKELIHOOD | IN_IMPACT (Inherent Impact), IN_LIKELIHOOD (Inherent Likelihood) | * (multiply) | Multiples the value of Inherent Impact by the value of Inherent Likelihood. | |
| KRI Status | TYPE==1 AND CV>LL AND CV<UL?1:2 | TYPE (Type), CV (Current Value), LL (Lower Limit), UL (Upper Limit) | == (equal to), and (logical and), > (greater than), < (less than), ? : (conditional expression) | Checks if the value of Type is 1, if the value of Current Value is greater than Lower Limit, and if the value of Current Value is less than Upper Limit. If yes, the formula outputs a value of 1, otherwise it outputs 2. | |
| Material Weaknesses | IC==2?1:0 | IC (Issue Classification) | == (equal to), ? : (conditional expression) | Checks if the value of Issue Classification is 2, If yes, the formula outputs a value of 1, otherwise it outputs 0. | |
| Maximum Likelihood | MAX(LIKELIHOOD) | LIKELIHOOD (Inherent Likelihood) | Max | Calculates the highest numeric value of Inherent Likelihood from objects in the Risks relationship. | |
| Net Loss | TOTALLOSS-TOTALRECOVERED | TOTALLOSS (Total Loss Amount), TOTALRECOVERED (Total Recovered Amount) | - (minus) | Subtracts the value of Total Loss Recovered from Total Loss Amount. | |
No. of Open Actions | SUM(OA) | OA (Open Actions) | Sum | Calculates the sum of values of Open Actions from objects in the Processes relationship. | |
| Percentage of Samples Failed | (SUM(FAILED))/(SUM(TESTED)*100 | FAILED (No. of Samples Failed) TESTED (No. of Samples Tested) Control | / (divide), * (multiply) | Sum | Divides the sum of values of No. of Samples Failed by the sum of No. of Samples Tested from objects in the Tests relationship, then multiplies the total by 100. |
| Percentage of Testing Complete | TESTING/PLANNED*100 | TESTED (No. of Samples Tested), PLANNED (Total Samples Planned) | / (divide), * (multiply) | Divides the value No. of Samples Tested by Total Samples Planned, then multiplies the total by 100. | |
| Time to Fix | timeDiff (DATECOMPLETED,DATEIDENTIFIED,"days") | DATECOMPLETED (Issue Resolution Date), DATEIDENTIFIED (Issue Identification Date) | timeDiff | Displays the difference, in days, between the Issue Resolution Date and the Issue Identification Date. | |
| Total Cost | QTY*COST | QTY (Quantity), COST (Cost) | * (multiply) | Multiples the value of Quantity by Cost. |