Calculated Metric Expressions Reference
Calculated metrics can contain references to other metrics, metadata in the form of field codes, operators, and functions. The tables in the sections below contain the operators and the functions available when writing expressions. See Accolade Field Codes Reference for information about the metadata available within Accolade. Note that certain project data field codes are not available for use in calculations. Use the equivalent function listed below instead.
Operators
Operators are process or mathematical parts of expressions, such as addition and multiplication, and relationships such as greater than or less than. Use the operators below within your calculated metric expressions.
Operator | Example |
---|---|
Additive and Subtractive Operators | |
+ (plus, plus sign) | {*METRIC:NPV*}+{*METRIC:fut*} |
- (minus, dash) | {*METRIC:NPV*}-{*METRIC:Cost*} |
Multiplicative Operators | |
* (multiplied by, asterisk) | 10*{*METRIC:NPV*} |
/ (divided by, slash) | {*METRIC:NPV*}/{*METRIC:fut*} |
% (mod, percent sign) | {*METRIC:NPV*}%10 |
Primary Operators | |
value (integers, text strings, dates, functions) |
('this is a text string') 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#). |
( ) (parentheses) | 1000-(6*{*METRIC:NPV*}) |
Relational Operators | |
= (equals) | {*METRIC:NPV*}=250 |
<> (does not equal) | {*METRIC:NPV*}<>{*METRIC:fut*} |
> (greater than, angle bracket) | {*METRIC:NPV*}>250 |
>= (greater than or equal to) | {*METRIC:NPV*}>=250 |
< (less than, angle bracket) | {*METRIC:Cost*}<500 |
<= (less than or equal to) | {*METRIC:Cost*}<=100 |
Logical Operators | |
or, || (double pipe) | {*METRIC:Cost*}<{*METRIC:NPV*}||{*METRIC:Cost*}<500 |
and, && | {*METRIC:Cost*}<{*METRIC:NPV*}&&{*METRIC:Cost*}<1000 |
Functions
The functions included in the calculated metric determine in part what is returned in the metric. Use the functions described below within your calculated expressions to return data within a calculated metric.
Note: All functions are supported in calculated metrics in Accolade Portfolio Optimizer unless otherwise noted.
Function | Description | Example | Result |
---|---|---|---|
Abs | Returns the absolute value of a specified number. | Abs(-1) | 1 |
Acos | Returns the angle whose cosine is the specified number. | Acos(1) | 0 |
Asin | Returns the angle whose sine is the specified number. | Asin(0) | 0 |
Atan | Returns the angle whose tangent is the specified number. | Atan(45) | 1 |
Avg | Returns the average of a set of values. | Avg(1, 5, 6) | 4 |
Ceiling | Returns the smallest integer greater than or equal to the specified number. | Ceiling(1.5) | 2 |
CellValue | Returns a value in the matrix column specified by a defined metric. | CellValue('Year') | The value in the matrix column with the matrix metric 'Year'. |
CellValue Format: CellValue('MatrixMetricsystemName') CellValue Parameters:
This function is valid for use in the filter expression argument of the GetMatrixColumn function, and will return values from the same row as the metric that uses the function. CellValue can only be used in matrix metrics, and can only reference matrix metrics. |
|||
Concatenate | Joins text strings into a single string in the specified order.
To include a delimiter between values in the concatenated string, use the Join function instead. You can concatenate as many strings together as you need. If a metric's calculated value extends beyond the allowable characters for the metric type, the data stored and displayed for that metric is truncated as follows:
|
Concatenate('text1',{*METRIC:MyString*}, 'text2', 'textN')
If the value of MyString = abc, the concatenated value would be text1abctext2textN. |
A continuous text string |
Contains | Returns whether one string is contained in another. | Contains('Blue', 'My Blue Heaven', 2, 7) | True |
Contains Format: Contains('findString', 'withinString', [startIndex, length]) Contains Parameters:
|
|||
ConvertToCorporateCurrency | Returns the value of the specified metrics in the defined corporate currency. | ConvertToCorporateCurrency({*METRIC:ProjectACurrency*}) | The ProjectACurrency metric value converted to the corporate currency |
ConvertToCorporateCurrency Format: ConvertToCorporateCurrency({*METRIC:systemName*}) ConvertToCorporateCurrency Parameters:
Additional ConvertToCorporateCurrency Examples:
Note that if using this formula, changes to the project currency code or the currency reference table containing exchange rates and conversion factors will result in a recalculation of all calculations using this formula. |
|||
Cos | Returns the cosine of the specified angle. | Cos(0) | 1 |
Count | Returns the number of elements in an expression.
'true' includes null values of the specified expression. |
Count(GetMatrixColumn('mxFinancials', 'Cost', Region = 'USA'), true) | A list of elements returned by the GetMatrixColumn expression including null values. |
Count Format: Count(<function returning an array>, <true/false>) Count Parameters:
|
|||
CurrentMatrixValue | Returns the current value of a metric. This function is the same as CurrentValue, but used in a matrix.
The CurrentMatrixValue function does not support nesting calculations in Accolade Portfolio Optimizer. This is a self-referencing function where the system name must be the system name of the metric calling the function. |
CurrentMatrixValue('Today') | The current value of the Today metric in a matrix cell |
CurrentMatrixValue Format: CurrentMatrixValue('systemName') CurrentMatrixValue Parameters:
Combine with other functions. For example: If({*METRIC:A*}='Current', Concatenate('Today', Now()), CurrentMatrixValue('Today')) If metric A is set to Current, the value in the matrix cell is set to Today, concatenated with today's date. For example, Today is 5/20/17. If metric A is set to anything other than Current, the value remains the same. Note that if using CurrentMatrixValue as a function in a matrix and updating the matrix in MS Excel, you must uniquely identify the row by flagging matrix metrics as unique. If matrix columns are not unique or the MS Excel workbook contains duplicate columns, the CurrentMatrixValue function value cannot be updated. The Smart Excel template does not update, upload, or publish back to Accolade. |
|||
CurrentValue | Returns the current value of a metric.
The CurrentValue function does not support nesting calculations in Accolade Portfolio Optimizer. This is a self-referencing function where the system name must be the system name of the metric calling the function. |
CurrentValue('Confidence') | The current value of the Confidence metric |
CurrentValue Format: CurrentValue('systemName') CurrentValue Parameters:
Combine with other functions. For example: if({*METRIC:NPV*}<500, 'At Risk', CurrentValue('Confidence')) If NPV is less than 500, the metric that contains this calculation is set to At Risk. If NPV is greater than 500, it is set to the current value in the Confidence metric. |
|||
DateAdd | Adds a number of time periods to a date. | DateAdd('D', 5, Now()) | 5 days added to current date |
DateAdd Format: DateAdd('type', number, #date#) DateAdd Parameters:
|
|||
DateDiff | Returns the selected difference between two dates. | DateDiff('D',ProjectNextGateDate(), ProjectStartDate()) | Days between project start and next gate |
DateDiff Format: DateDiff('type', #endDate#, #startDate#) DateDiff Parameters:
|
|||
DateFormat | Returns a date in the specified date format as a string. | DateFormat(ProjectCreationDate(), 'M/d/yyyy') | Date the project was created in M/d/yyyy format. |
DateFormat Format: DateFormat(#date#, 'dateFormat') DateFormat Parameters:
Supported date formats are those set in the Accolade user profile, included the following: M/d/yyyy, MMM dd, yyyy, d/M/yyyy, dd/MM/yyyy, d.M.yyyy, d-M.yyyy, yyyy-MM-dd, yyyy/M/d, yyyy.M.d. Combine with other functions. For example: DateFormat(Now(),'M/d/yyyy') returns today's date such as 10/15/2018. |
|||
DatePart | Returns the selected part of a date. | DatePart('M', #5/15/2013#) | 5 |
DatePart Format: DatePart('type', #date#) DatePart Parameters:
|
|||
Exp | Returns e raised to the specified power | Exp(0) | 1 |
Find | Returns how far from the start of a string a contained string was found. Case sensitive. | Find('Blue', 'My Blue Heaven', 2, 20) | 4 |
Find Format: Find('findString', 'withinString', [startIndex, length]) Find Parameters:
|
|||
First | Returns the first value that is returned in a list of values. | First(1, 5, 6) | 1 |
Floor | Returns the largest integer less than or equal to the specified number. | Floor(1.5) | 1 |
GateDecision | Returns the decision for the specified gate. | GateDecision(2) | A string of the decision for the second gate in the project |
GateDecision Format: GateDecision('1-20') or GateDecision({*METRIC:systemName*}) Gate Decision Parameters:
|
|||
GetMatrixColumn | Returns an array from a matrix.
This function must be used with the Join, Concatenate, Count, Avg, First, Min, Max, or Sum function to perform a calculation on the values returned from the matrix column. |
Sum(GetMatrixColumn('mxSales', 'Profit', (Market = 'US' || Market = 'Europe') && Profit > 10000)) | In the 'mxSales' matrix, the function returns the sum of profit for the US and Europe markets greater than 10,000 USD. |
GetMatrixColumn Format: GetMatrixColumn('matrixSystemName', 'targetColumnSystemName', filterExpression) GetMatrixColumn Parameters:
Additional GetMatrixColumn Examples:
|
|||
IEEERemainder | Returns the remainder resulting from the division of a specified number by another specified number. | IEEERemainder(13, 5) | 3 |
If | Returns a specified string or value based on a condition. | If({*METRIC:NPV*}>500, 'Go ahead', 'Stop') | Go ahead |
In | Returns whether an element is in a set of values. | In(1+1, 1, 2, 3) | True |
IRR | Returns the Internal Rate of Return value for a series of cash flows. | IRR(GetMatrixColumn('mxFinancials', 'Cost', Region = 'USA')) | Returns the internal rate of return value for the cash flows returned by the GetMatrixColumn function. |
IRR Format: IRR(value array, [guess]) IRR Parameters:
|
|||
IsNull | Checks if the first parameter is an empty value.
If the parameter value is null, returns the second parameter value; otherwise, returns the first parameter value. |
IsNull(ProjectEndDate(), {*METRIC:FinanceStartDate*}) | Finance start date if the project end date is not defined |
IsNull Format: isNull({parameter1},{parameter2}) IsNull Parameters:
|
|||
Join | Joins text strings together into a single string in the specified order, separated by a delimiter.
For example, create a metric using this function to create a list of the child projects for a particular project, sending in '|' as the delimiter. Then create a multi-select list that uses the resulting project names to select from. To join text strings with other provided values, use the Concatenate function. |
Join(LinkedProjectValues('children', 'to', 'Code'), '|') | A list of values separated by |. |
Join Format: Join(<function returning an array>, '<delimiter>') Join Parameters:
|
|||
Length | Returns the length of a string. | Length({*METRIC:myString*}) | An integer |
LinkedProjectValues | Returns the values of the specified metrics for linked projects.
This function must be used with the Join, Concatenate, Count, Avg, First, Min, Max, or Sum function to perform a calculation on the values returned from the linked projects. Use this function to calculate roll up values in projects that contain one or more related projects. The LinkedProjectValues function is not supported in Accolade Portfolio Optimizer. Metrics that contain this function cannot be set as matrix metrics. |
Sum(LinkedProjectValues('ABToCSG','To','NPV',5)) (see below for additional examples) |
Totals the values in the NPV metric in projects that are linked to the project using the ABToCSG link type. This example totals values in projects up to five levels deep. |
LinkedProjectValues Format: LinkedProjectValues('linkTypeSystemName','direction','returnMetricSystemName',traverseLevel) LinkedProjectValues Parameters:
Additional LinkedProjectValues Examples:
|
|||
Log | Returns the logarithm of a specified number for a specified base. | Log(1, 10) | 0 |
Log10 | Returns the logarithm of a specified number to base 10. | Log10(1) | 0 |
Lower | Returns a text string with all lower case. | Lower('Hello') | hello |
LTrim | Removes leading spaces from a string. | LTrim({*METRIC:myString*}) | A string without a leading space |
Max | Returns the largest of the specified values. | Max(1, 5, 6) | 6 |
Min | Returns the smallest of the specified values. | Min(1, 5, 6) | 1 |
ModelName | Returns the name of the model. | ModelName() | |
MostRecentGateDecision | Returns the gate decision from the most recent phase change. | MostRecentGateDecision() | |
Now | Returns the current date and timestamp. | Now() | |
NPV | Returns the net present value of a series of cash flows based on a specified discount rate. | NPV(.12, 10, 12, 8, 16) | 34.4 |
NPV Format: NPV(rate, <series of cash flow values>) NPV Parameters:
Additional NPV Examples:
|
|||
PaybackPeriod | Returns the payback period. | PaybackPeriod('cash_flow', '~Net_Cash_Flow', '~Cash_Flow_Relative_Date') |
A decimal number displaying the payback period in years. If the payback period cannot be calculated, the function returns 0. |
Payback Period Parameters:
|
|||
Pow | Returns the value of a number raised to the specified power. | Pow(3, 2) | 9 |
ProjectAccessGroup | Returns the name of the project's access group. | ProjectAccessGroup() | |
ProjectClass | Returns the name of the project's class. | ProjectClass() | |
ProjectClosed | Returns the project's closure status. | ProjectClosed() | |
ProjectCreatedByID | Returns the ID of the user that created the project. | ProjectCreatedByID() | |
ProjectCreatedByName | Returns the name of the user that created the project. | ProjectCreatedByName() | |
ProjectCreationDate | Returns the date when the referenced project was created. | ProjectCreationDate() | |
ProjectCurrentPhase | Returns the system ID of the current/stage/gate pair in the referenced project.
The project current phase is set to 0 if the project ends in a gate, and the gate's decision is set to Go. |
ProjectCurrentPhase() | |
ProjectCurrentStageName | Returns the name of the current project stage. | ProjectCurrentStageName() | |
ProjectDescription | Returns the description of the project from the model. | ProjectDescription() | |
ProjectEndDate | Returns the date the project ended. | ProjectEndDate() | |
ProjectGateDate | Returns the date of the gate meeting of a specific gate. | ProjectGateDate(1) | The date for the first gate meeting in the project |
ProjectGateDate Format: ProjectGateDate('1-20') or ProjectGateDate({*METRIC:systemName*}) ProjectGateDate Parameters:
|
|||
ProjectHealthScore | Returns the project's health score from the connected Acclaim Projects project. | ProjectHealthScore() | The project health score from Acclaim Projects. |
ProjectID | Returns the user-created ID of the referenced project. | ProjectID() | |
ProjectMostRecentStatus | Returns the text of the most recent status report on the referenced project. | ProjectMostRecentStatus() | |
ProjectMostRecentStatusAuthor | Returns name of the person who created the project's most recent status report. | ProjectMostRecentStatusAuthor() | |
ProjectMostRecentStatusDate | Returns the date the project's most recent status report was created. | ProjectMostRecentStatusDate() | |
ProjectName | Returns the name of the project. | ProjectName() | |
ProjectNextGateDate | Returns the date of the next gate meeting in the project. | ProjectNextGateDate() | |
ProjectNextGateName | Returns the name of the next gate in the project. | ProjectNextGateName() | |
ProjectNextGateOwnerName | Returns the name of the gate owner of the project's next gate. | ProjectNextGateOwnerName() | |
ProjectPreviousGateDate | Returns the date of the gate meeting before the current stage. | ProjectPreviousGateDate() | |
ProjectStartDate | Returns the date when the project started. | ProjectStartDate() | |
ProjectTeamLeaderEmail | Returns the email address of this project's Project Manager. | ProjectTeamLeaderEmail() | |
ProjectTeamLeaderName | Returns the name of this project's Project Manager. | ProjectTeamLeaderName() | |
ReferenceTable | Returns the value from the specified cell of a reference table.
If a calculated metric uses the ReferenceTable function, the reference table must be marked as Available to Portfolio Optimizer for the metric calculation to work in Portfolio Optimizer. |
ReferenceTable('Currency', 'CurrencyCode', 'USD', 'Value2015') | The value from the Value2015 column for the given filter |
ReferenceTable Format: ReferenceTable('RefTableSystemName', 'sourceColumn1', 'sourceColumnValue1', 'sourceColumn2', 'sourceColumnValue2'...'targetColumn') ReferenceTable Parameters:
The formula returns back the first matching value ordered by row id. The ReferenceTable function can include an infinite amount of column/value pairs in the expression. Note that they must exist as a pair containing the column system name and the associated value expression. |
|||
Replace | Replaces every instance of a text found in a larger string with a new text and returns the string. Case sensitive. | Replace('My Blue Heaven', 'Blue', 'red') | My red heaven |
Replace Format: Replace('originalString', 'oldMatch', 'newText') Replace Parameters:
|
|||
ResourceDemands | Returns all the resource demands on a project or all demands between two dates.
The ResourceDemands function is not supported in Accolade Portfolio Optimizer. |
ResourceDemands(ProjectGateDate (2), ProjectGateDate (3)) | Sum of all demands between these dates, ignoring units of measure |
ResourceDemands Format: ResourceDemands([#startDate#], [#endDate#]) ResourceDemands Parameters:
|
|||
ResourceDemandsForPool | Returns all of a project's resource demands for a specified pool.
If dates are included, the sum is for demand between the start and end dates. The ResourceDemandsForPool function is not supported in Accolade Portfolio Optimizer. |
ResourceDemandsForPool(5, #4/1/2014#, #7/1/2014#) | Sum of demands on the specified pool between the specified dates |
ResourceDemandsForPool Format: ResourceDemandsForPool(poolID, [#startDate#, #endDate#]) ResourceDemandsForPool Parameters:
|
|||
ResourceDemandsForUOM | Returns all of this project's demands that have the specified unit of measure.
The ResourceDemandsForUOM function is not supported in Accolade Portfolio Optimizer. |
ResourceDemandsForUOM('FTE', #4/1/2014#, #7/1/2014#) | Sum of demands on the pools with the specified UOM |
ResourceDemandsForUOM Format: ResourceDemandsForUOM('Unit of Measure', [#startDate#, #endDate#]) ResourceDemandsForUOM Parameters:
|
|||
Round | Rounds a value to the nearest integer or specified number of decimal places. | Round(3.257, 2) | 3.26 |
RTrim | Removes trailing spaces from a string. | RTrim({*METRIC:myString*}) | A string without trailing spaces |
RowValue | Returns a value in the matrix column specified by the defined offset parameter.
This function allows you to prorate across a matrix to identify values in certain cells. This is a self-referencing function that can only be used in matrices. |
RowValue(-1,'mxYear2financials') | The value in the cell prior to the 'mxYear2financials' column to which the metric is applied. |
RowValue Format: RowValue('Offset', 'ColumnSystemName') RowValue Parameters:
These parameters cannot be nested functions or use substitution tokens. |
|||
Search | Returns how far from the start of a string a contained string was found. Case insensitive. | Search('blue', 'An attractive Aqua Blue dye', 5, 30). | 20 |
Search Format: Search('findString', 'withinString', [startIndex, length]) Search Parameters:
|
|||
SecurityList | Returns either the security list value display name or the security list value system name at a specified level in the list. | SecurityList(1,3,2,2) | Returns the first two security list display names in the second level of security list three. |
SecurityList Format: SecurityList(returnValueType,listNumber,listLevel,maxReturnCount) SecurityList Parameters:
Additional SecurityList Examples: ALL Locations Using the security list defined above, for a project that is assigned Europe, Paris, and USA:
|
|||
Sign | Returns a value indicating the sign of a number. | Sign(-13) | -1 |
Sin | Returns the sine of the specified angle. | Sin(0) | 0 |
Sqrt | Returns the square root of a specified number. | Sqrt(4) | 2 |
SubString | Returns a string beginning a specified distance from the start of the string that contains it. | SubString('My Blue Heaven', 4, 10) | Blue Heaven |
SubString Format: SubString(withinString, startIndex, [length]) SubString Parameters:
|
|||
Sum | Totals the returned values. | Sum(1, 5, 6) | 12 |
SysProjectID | Returns the system ID of the reference project. | SysProjectID() | |
Tan | Returns the tangent of the specified angle. | Tan(0) | 0 |
TeamMember | Returns a team member's name, function, email address, or user login. | TeamMember(2,'FinanceLeader',3) | The team member's name that is assigned to the Finance Leader function in the project. |
TeamMember Format: TeamMember(lookupMethod,'lookupValue',returnValueType) TeamMember Parameters:
Additional TeamMember Examples:
|
|||
Trim | Removes all spaces from a string except for single spaces between words. | Trim({*METRIC:myString*}) | A string without leading or trailing spaces |
Truncate | Returns the integral part of a number. | Truncate(1.7) | 1 |
Upper | Returns a text string with all upper case. | Upper('Hello') | HELLO |