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:

  • MatrixMetricSystemName - the system name of the matrix metric to reference. This must be the system name, and cannot be a substitution token.

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:

  • String - 500 characters
  • Long String - 2000 characters
  • Number - 15 characters
  • Multi-Select List - 2500 characters
  • List - 2500 characters

 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:

  • findString - The string to find.
  • withinString - The string in which to search for the find.
  • String startIndex - (Optional) Number of characters from the beginning of within. Minimum value is 1.
  • length - (Optional) Number of characters from the startIndex to search.
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:

  • Field code or integer value - The value being converted to the corporate currency. This typically includes the metric system name or metadata field code assigned to the values to convert and return as the corporate currency. It can also be an integer to convert.
  • Valid currency code - (Optional) A valid currency code other than the corporate currency to convert the specified values to. This can also be a metadata field code for a project currency.

Additional ConvertToCorporateCurrency Examples:

  • ConvertToCorporateCurrency(CellValue('MatrixMetricsystemName'))
  • ConvertToCorporateCurrency(1000)
  • ConvertToCorporateCurrency({*METRIC:systemName*}, 'EUR')
  • ConvertToCorporateCurrency(1000, {*MD:ProjectCurrencyCode*})

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.
'false' excludes null values.

 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:

  • Function returning an array - A function that returns a list of values. Use LinkedProjectValue and GetMatrixColumn as a best practice.
  • true or false - Determines whether to include null values or not. The true or false value must be lower case and not include quotes around it.
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:

  • systemName - The system name assigned to the metric calling the function to return the current value.

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:

  • systemName - The system name assigned to the metric calling the function to return the current value.

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:

  • type - D: Day, H: Hour, MN: Minute, S:Second, M: Month, Y: Year.
  • number - An integer.
  • date - The date to adjust, can be a date metric or the Now function.
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:

  • type - D: Day, H: Hour, MN: Minute, S: Second, M: Month, Y: Year.
  • endDate - The ending date.
  • startDate - The starting date.
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:

  • date - A specified date or function indicating a date.
  • dateFormat - Date format in which to return the value. The inputs allowed for this parameter are strings using string-type metrics.

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:

  • type: D - Day, H: Hour, MN: Minute, S: Second, M: Month, Y: Year.
  • date - The date containing the part you want.
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:

  • findString - The string to find.
  • withinString - The string in which to search for the findString.
  • startIndex - (Optional) Number of characters from the beginning of withinString to start searching. Minimum value is 1.
  • length - (Optional) Number of characters from the startIndex to search.
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:

  • 1-20 - An integer value correlating to the gate on the project. 1 indicates the first gate on the project.
  • Metric field code system name - The referenced field code must return an integer value 1-20.
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:

  • matrixSystemName - The system name of the matrix. This must be the system name, and cannot be a substitution token or nested function.
  • targetColumnSystemName- The system name of the column to return values.
  • filterExpression - (Optional) Inputs used to filter columns in the matrix. Elements that meet the search criteria are returned as an array. Valid inputs include column system names, metric and metadata tokens, IsNull, and hard-coded values. The expression must equate to true or false. Use operators to join filter expressions for more complex filtering.

Additional GetMatrixColumn Examples:

  • Count(GetMatrixColumn('mxSales', 'Profit', SalesYear >= 2010 && Profit < 0), true) - Returns the number of sales years since 2010 that had a negative profit.
  • Join(GetMatrixColumn('mxSales', 'Market', Profit > 0), ', ') - Returns all markets in the 'mxSales' matrix that made a profit.
  • Sum(GetMatrixColumn('mxSales', 'Profit', IsNull(Costs, 0) > 1000)) - Returns the sum of profit where the costs exceeded 1000.
  • Sum(GetMatrixColumn(('mxSales', 'Cost', Year = CellValue(#Year#))) - Returns the total costs by year.
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:

  • Value Array - An array passed in as an object specifying cash flow values. The array must contain at least one positive value (a receipt) and one negative value (a payment). GetMatrixColumn is currently the only function that can be passed in as an object and return an array. If the data is not valid, the calculation will be skipped. Invalid data is defined as the following:
    • No values
    • Array of strings or date values
    • Only positive values
    • Only negative values
    • Only zeros
  • Guess - (Optional) A value you estimate will be returned by IRR. If omitted, guess is 0.1 (10 percent). If the data is not valid, the calculation will be skipped. Invalid data is defined as the following:
    • Guess value of -1 or lower
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:

  • parameter1 - The parameter to check for no value.
  • parameter2 - If no value in parameter1, sets this parameter.
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:

  • Function returning an array - A function that returns a list of values from the same object. The LinkedProjectValues function and GetMatrixColumn are the only functions that return an array.
  • Delimiter - Any character to separate returned values, such as a pipe ( | ).
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.

Metrics that contain this calculation cannot be used when creating automatic project linking rules. When applying a metric that uses this formula, consider where calculations take place. For example, if a parent project and its child projects use the same model that contains a metric with this calculation, the calculated metric recalculates for all projects in the chain.

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:

  • linkTypeSystemName - The system name of the link type used to create the project links. To specify the hierarchy link type, use HierarchyLinkDefault as the system name. This must be the system name, and cannot be a substitution token.
  • direction - The direction to or from the project that contains the calculation to gather and return values. To traverses down the hierarchy; From traverses up the hierarchy. If your values do not calculate correctly, ensure that the project links were created in the appropriate direction.
  • returnMetricSystemName - The system name of the metric value to return. This must be the metric system name, and cannot be a substitution token.
  • traverseLevel - (Optional) The number of levels from the project that contains the calculation to gather and return values. If no value is included, values are returned for all levels in the hierarchy.

Additional LinkedProjectValues Examples:

  • Sum(LinkedProjectValues('ABToCSG','To','NPV')) - Returns the total of all values entered in the NPV metric in projects that are linked to the parent project using the ABToCSG link type.
  • Avg(LinkedProjectValues('ABToCSG','To','NPV',3)) - Returns the average of the NPV metric in projects that are linked up to three levels deep in the hierarchy to the parent project using the ABToCSG link type.
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:

  • Rate - The discount rate.
  • Series of cash flow values - Series of values separated by a comma delimiter. Can also be an array passed in as an object or a series of metric values.

Additional NPV Examples:

  • NPV(.12, GetMatrixColumn('mxFinancials', 'Cost', Region = 'USA')) - Returns the net present value of the cash flows returned by GetMatrixColumn based on a 12% discount rate.
  • NPV(.12, {*METRIC:Year1Financials*}, {*METRIC:Year2Financials*} - Returns net present value of Year1Financials and Year2Financials based on a 12% discount rate.
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:

  • Financial matrix - Captures costs and revenue.
  • Cash Flow - The difference between total revenue and total costs.
  • Relative Date - The relative period of time in which costs and revenue are recorded.
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:

  • 1-20 - An integer value correlating to the gate on the project. 1 indicates the first gate on the project.
  • Metric field code system name - The referenced field code must return an integer value 1-20. For example, ProjectGateDate('{*METRIC:NumberMetric*}') would return an integer value between 1-20.
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:

  • RefTableSystemName - The system name of the reference table. This must be the system name, and cannot be a substitution token or nested function.
  • sourceColumn1 - The system name of the column from which to find values.
  • sourceColumnValue1 - The value within the column being pointed to. Must be added with the sourceColumn1 parameter.
  • sourceColumn2 - (Optional) The system name of the second column from which to find values.
  • sourceColumnValue2 - (Optional) The value within the second column being pointed to.
  • targetColumn - The system name of the column to return values.

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:

  • originalString - The string that is searched.
  • oldMatch - The string that is replaced.
  • newText - The string that replaces the oldMatch.
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:

  • startDate - (Optional) The date on which to begin counting demands.
  • endDate - (Optional) The date on which to stop counting demands.
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:

  • poolID - The ID of the pool to calculate demands on. You can view the ID in a report on the RV_ResourcePools view.
  • startDate - (Optional) The date on which to begin counting demands.
  • endDate - (Optional) The date on which to stop counting demands.
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:

  • Unit of Measure - The unit of measure of the pools to include.
  • startDate - (Optional) The date on which to begin counting demands.
  • endDate - (Optional) The date on which to stop counting demands.
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:

  • Offset - An integer less than 0. Any negative value indicates the number of rows above the current row to retrieve a value from. For example, if the formula is being evaluated for row 4, and you need the value in row 3, use -1 as the offset value. Used to propagate down a matrix column or across a row in inter-row dependencies. To reference the current row, use a matrix metric token.
  • Column System Name - The system name of the matrix column containing the cell to reference.

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:

  • findString - The string to find.
  • withinString - The string in which to search for the findString.
  • startIndex - (Optional) Number of characters from the beginning of withinString to start searching. Minimum value is 1.
  • length - (Optional) Number of characters from the startIndex to search.
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:

  • returnValueType - Either 1 to return the security list display name, or 2 to return the security list system name.
  • listNumber - The security list to use. Acceptable values are 1, 2, 3, 4, or 5. The number entered must correspond to an existing security list. For example, if your system configuration only uses three security lists, acceptable values are 1, 2, or 3.
  • listLevel - The level in the security list tree to return values from. If the returned value exceeds 500 characters, the returned value includes as many complete security list level names that fit within 500 characters. This cannot be a substitution token.
  • maxReturnCount - The number of values to return in a delimited string, if more than one value is found in the security list and level specified. A maximum of 500 characters is returned. Only full security list level names are returned.

Additional SecurityList Examples:

   ALL Locations
       Europe
            France
                  Paris
                  Lille
            England
                  London
                  Birmingham
       North America
            Canada
                  Montreal
                  Calgary
            USA
                  Denver
                  Houston

Using the security list defined above, for a project that is assigned Europe, Paris, and USA:

  • SecurityList(1,1,2,2) - Returns Europe|North America
  • SecurityList(1,1,3,2) - Returns France|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:

  • withinString - The string that contains the substring.
  • startIndex - Number of characters from the beginning of withinString that the substring begins. Minimum value is 1.
  • length - (Optional) Number of characters to include in the returned substring. If not present, returns all characters after start index.
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:

  • lookupMethod - The method used to match a team member to return. 1: row Index, 2: function system name.
  • lookupValue - The value of the index or function you want to use to find the team member. If the lookupMethod is set to 1, enter an integer number representing the row in a list of team members arranged in alphabetic order by team member first name. Any functions without a team member assigned are sorted to the top of the list, and assigned team members follow in alphabetical order. Therefore, the returned team member could be None, indicating no team member is assigned at that row. If the lookupMethod is set to 2, enter the function system name to return the value for the primary team member for that function. If no primary user is associated to the function, no value is returned.
  • returnValueType - The information to return for the matched team member. 1: Function Name, 2: Email, 3: Name, 4: User Login.

Additional TeamMember Examples:

  • TeamMember(1,3,3) - Returns the name of the team member that is listed in the third from of an alphabetical list of team members. If the third row is a function with no leader, no name is returned. Note that the lookupValue does not have to be in enclosed in single quotes when looking up by row index.
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
Learn More: