Calculated Metric Expressions Overview
Using calculated expressions within a metric allows you to create simple to complex metric values using operators and functions. Expressions are typically based on the value of one or more pieces of data defined in Accolade, which can be references to other metrics or calculated metrics, or project metadata available in the system in the form of field codes, operators, and functions.
Metrics assigned to calculate on any associated change are recalculated when any of the following occur:
- A user uses the Recalculate button dialog on the Metrics page (Process > Configuration > Metrics). To recalculate all metrics in the system, click Recalculate All on opening the dialog, otherwise select a subset of metrics and click Recalculate.
- A user clicks Apply on a page that contains the metric.
- A Process Designer clicks Recalculate Project from within a project.
- A Smart Excel document that contains the metric is uploaded and published.
- A metric that the calculated metric is based on is updated.
- The expression of the calculated metric is changed in the metric definition.
- A reference table containing one or more calculated metrics is updated.
Metrics assigned to calculate at a scheduled time or event will only calculate when specified.
Calculated Expression Format
The format of the expressions you create depends on the expression itself. However, keep the following in mind as you build your expressions:
- To base a calculated metric on the value of another metric, add a metric to the expression with the following format: {*METRIC:system name*}. For example, for a metric whose system name is "NPV", the code is {*METRIC:NPV*}. Field codes are not case sensitive.
- To base a calculated metric on the value of a metadata field code, add metadata to the expression with the following format: {*MD:item name*}. For example, to include the project start date, the code is {*MD:ProjectStartDate*}. See Accolade Field Codes Reference for a list of available metadata field codes.
- If using a hard date value inside of a calculated expression, surround the value with # characters to ensure the computer recognizes it as a date. As best practice, use YYYY/MM/DD date format. For example, you want to check to see if a date metric has a certain value and if not, set a default date to use in comparison. Use the calculated expression if(isNull(dateMetric1, #1900/01/01#) < isNull(dateMetric2, #1990/01/01#), 0, 1). The expression compares two dates and sets the value to 0 if the first date metric value is less than the second date metric value. If not, the value is 1 or replaces the date with January 1, 1900 if the date metric is not set.
- Enclose column system name parameters for GetMatrixColumn() in square brackets. This is the only function that supports square brackets, do not use square brackets in calculations. They are used in examples in the help to indicate optional function parameters.
- Enclose parameters, strings, and static dates in single quotes.
While single quotes work for dates, we recommend wrapping dates in pound or hash signs instead to ensure consistency of data types. For example, (#2021-10-19#).
Example
Your company has a metric called YearEndProfit that indicates the profit for a project for a year. YearEndProfit is a calculated metric that uses the Number type and contains the following calculated expression:
({*METRIC:Price*}-{*METRIC:Cost*})*{*METRIC:UnitForYr*}
The above expression takes the value of other metrics defined in the system to calculate the profit for the year. The value of Price, subtracted by cost, multiplied by the unit for the year.
Your company may also have a metric called TotalProjectDays to determine how many total days a project requires to complete, which also may then factor into other metrics around costs of full time resources, etc. TotalProjectDays is a calculated metric that uses the Number type and contains the following calculated expression:
DateDiff('D',{*MD:ProjectEndDate*}, {*MD:ProjectStartDate*})
The above expressions uses the system metadata for the project start and end dates. The DateDiff function, using the parameter D to indicate Days, returns the number of days between the project start date and project end dates. You can also choose to return the time in Months or Years change the Type parameter within the expression to M or Y, respectively.
Calculated Expressions in Portfolio Optimizer
Calculated metrics that are available in Portfolio Optimizer must meet the following requirements:
- Have the Available to Portfolio Optimizer option selected in the metric definition.
- Contain metrics that also have the Available to Portfolio Optimizer option selected. This applies to all metrics in the calculation.
- Contain metrics that are in at least one project in Portfolio Optimizer scenario to generate a value for the calculation.
- If a calculated metric uses the ReferenceTable function, the respective reference table must also be marked as Available to Portfolio Optimizer for the metric calculation to work in Portfolio Optimizer.
|
Notes:
|