Formula List

Performance Quoting Formula

Performance Quoting Formula engine leverages the core functions available also in the Pricing systems.

The quote model logic requires additional functions:

Aggregation Functions
Performance Quoting Functions

This is the list of formulas that can be used on cells. See Data Model - Columns, Lines & Fields for details.

Core Functions

ABS

Computes the absolute value, where negative values are converted to positive values with equivalent magnitudes (only the sign changes) and non-negative values are returned as-is.

RETURNS	FUNCTION SIGNATURES
decimal	ABS(@Nullable decimal number) Examples: ABS(1.23) = 1.23 ABS(-1.23) = 1.23 * ABS(NULL(0)) = null

Input Arguments:

number
Any numeric value; if null, the result of the function will also be null.

ACOS

Computes the arccosine, the inverse of the COS function, returning the angle (in radians) that has the cosine matching the given value. The returned angle may be converted using the DEGREES function.

RETURNS	FUNCTION SIGNATURES
decimal	ACOS(decimal value)

Input Arguments:

value
Scalar decimal value between -1.0 and +1.0, inclusive.

ADD_TIME (DEPRECATED)

Adds the specified number of hours, minutes, and optionally seconds to the specified basis value. The hours, minutes, and seconds must be expressed as non-null exact integers with no currency or units. See also: ADJUST_TIME

RETURNS	FUNCTION SIGNATURES
datetime	ADD_TIME(@Nullable date dateValue, decimal hours, decimal minutes)
datetime	ADD_TIME(@Nullable datetime dateTimeValue, decimal hours, decimal minutes)
datetime	ADD_TIME(@Nullable date dateValue, decimal hours, decimal minutes, decimal seconds)
datetime	ADD_TIME(@Nullable datetime dateTimeValue, decimal hours, decimal minutes, decimal seconds)

Input Arguments:

dateTimeValue
The DATETIME value to use as the basis for computing the new DATETIME.

dateValue

The DATE value to use as the basis for computing the new DATETIME.

hours

The number of hours to add to the basis value. The value must be a non-null exact integer with no currency or

units. A negative value will be subtracted from the basis, and values greater than 24 will result in adding

additional days to the basis.

minutes

The number of minutes to add to the basis value. The value must be a non-null exact integer with no currency

or units. A negative value will be subtracted from the basis, and values greater than 60 will result in

adding additional hours to the basis.

seconds

The number of seconds to add to the basis value. The value must be a non-null exact integer with no currency

or units. A negative value will be subtracted from the basis, and values greater than 60 will result in

adding additional minutes to the basis.

ADJUST_TIME

Adjusts a date or date-time forward or back by one or more date and/or time increments. If the same date or time field is repeated multiple times in the same function call, the increments for that field will be summed prior to the adjustment. When multiple fields are adjusted in the same function call, adjustments will be applied in order from least (SECONDS) to greatest (YEARS) significance.

RETURNS	FUNCTION SIGNATURES
datetime	ADJUST_TIME(date dateValue, TimeField field, decimal adjustment, [TimeField field, decimal adjustment]...)
datetime	ADJUST_TIME(datetime dateTimeValue, TimeField field, decimal adjustment, [TimeField field, decimal adjustment]...)

Input Arguments:

adjustment
Scalar integer amount (positive or negative) to apply as the adjustment to the date or time component

specified by the 'field' parameter.

dateTimeValue

Adjustments will be applied to this starting date and time.

dateValue

Adjustments will be applied to this starting date, beginning from midnight (00:00:00).
field (keyword type: TimeField)
Allowed values: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS
The date or time component to adjust: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS

ASIN

Computes the arcsine, the inverse of the SIN function, returning the angle (in radians) that has the sine matching the given value. The returned angle may be converted using the DEGREES function.

RETURNS	FUNCTION SIGNATURES
decimal	ASIN(decimal value)

Input Arguments:

value
Scalar decimal value between -1.0 and +1.0, inclusive.

ATAN

Computes the arctangent, the inverse of the TAN function, returning the angle (in radians) that has the tangent matching the given value. The returned angle may be converted using the DEGREES function.

RETURNS	FUNCTION SIGNATURES
decimal	ATAN(decimal value)

Input Arguments:

value
Scalar decimal value.

AVERAGE

Returns the average (arithmetic mean) for the list of numbers provided as the parameters of the function.

RETURNS	FUNCTION SIGNATURES
decimal	AVERAGE(decimal[...] numberList) Returns the average (arithmetic mean) of the numbers contained in the provided list. All numbers provided must have the same units and / or currency, and must also not be null. If the numbers do not meet both conditions, the evaluation will result in an error.
decimal	AVERAGE(decimal firstNumber, [decimal otherNumbers]...) Returns the average (arithmetic mean) for the list of numbers provided as the parameters of the function. All numbers provided must have the same units and / or currency, and must also not be null. If the numbers do not meet both conditions, the evaluation will result in an error.
decimal	AVERAGE(NullIndicator behaviorForNulls, decimal[...] numberList) Returns the average (arithmetic mean) of the numbers contained in the provided list, while ignoring any null values. If all of the numbers in the list are null, the function returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

RETURNS	FUNCTION SIGNATURES
decimal	AVERAGE(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumbers]...) Returns the average (arithmetic mean) for the list of numbers provided as the parameters of the function, while ignoring any null values found. If all of the numbers provided in the list are null, the function also returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

RETURNS

FUNCTION SIGNATURES

decimal

AVERAGE(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumbers]...) Returns the average (arithmetic mean) for the list of numbers provided as the parameters of the function, while ignoring any null values found. If all of the numbers provided in the list are null, the function also returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

Input Arguments:

behaviorForNulls (keyword type: NullIndicator) Allowed values: IGNORE_NULL
Use the IGNORE_NULL keyword to accept (and ignore) null values instead of generating an error. Currently,
IGNORE_NULL is the only legal value.
firstNumber
The first number in the average, must not be null unless IGNORE_NULL is also specified.
numberList
A LIST(...) of numbers to be averaged. For the IGNORE_NULL overload, null values in the list are skipped;
otherwise, null values are not allowed.
otherNumbers
The subsequent number(s) to be averaged, must have the same units and currency as the first number, and must
not be null unless IGNORE_NULL is also specified.

CHOOSE

Return the value specified by the index in the provided list of values. All the values in the list must be of the same type, which also serves as the return type of the function. The first value in the list is at index 1, through to the last value in the list. If the index is less than one or greater than the number of values in the list, an error is raised. If a value is specified for the index that contains a

fractional component, the fractional component of the value is dropped (truncated) for the purposes of determining the index of the value to return.

BOOLEAN	CHOOSE(DECIMAL INDEX, BOOLEAN[...] VALUESLIST)
boolean	CHOOSE(decimal index, @Nullable boolean firstValue, [@Nullable boolean otherValues]...)
currency	CHOOSE(decimal index, currency[...] valuesList)
currency	CHOOSE(decimal index, @Nullable currency firstValue, [@Nullable currency otherValues]...)
date	CHOOSE(decimal index, date[...] valuesList)
date	CHOOSE(decimal index, @Nullable date firstValue, [@Nullable date otherValues]...)
datetime	CHOOSE(decimal index, datetime[...] valuesList)
datetime	CHOOSE(decimal index, @Nullable datetime firstValue, [@Nullable datetime otherValues]...)
decimal	CHOOSE(decimal index, decimal[...] valuesList)
decimal	CHOOSE(decimal index, @Nullable decimal firstValue, [@Nullable decimal otherValues]...)
dictionary	CHOOSE(decimal index, dictionary[...] valuesList)
dictionary	CHOOSE(decimal index, @Nullable dictionary firstValue, [@Nullable dictionary otherValues]...)

BOOLEAN	CHOOSE(DECIMAL INDEX, BOOLEAN[...] VALUESLIST)
string	CHOOSE(decimal index, string[...] valuesList)
string	CHOOSE(decimal index, @Nullable string firstValue, [@Nullable string otherValues]...)
unit	CHOOSE(decimal index, unit[...] valuesList)
unit	CHOOSE(decimal index, @Nullable unit firstValue, [@Nullable unit otherValues]...)

Input Arguments:

firstValue
The first value in the list of possible values to return

index

The position of the desired value

otherValues

Additional values in the list of possible values to return

valuesList

A LIST of values to choose from. Must contain at least one element.

COERCE

Forces the supplied decimal value to be assigned the provided currency and/or unit. This does not perform currency or unit conversions, use with care! The numeric value will not be altered but the result will be interpreted with the new currency and/or unit in subsequent operations. Currency and/or unit can be removed by this function by passing the CURRENCY or UNIT functions, respectively, as inputs using the special code "" (empty string). See also: CONVERT, SCALAR

RETURNS	FUNCTION SIGNATURES
decimal	COERCE(currency currencyValue, @Nullable decimal decimalValue)
decimal	COERCE(unit unitValue, @Nullable decimal decimalValue)
decimal	COERCE(currency currencyValue, unit unitValue, @Nullable decimal decimalValue)

Input Arguments:

currencyValue
The currency to set for the decimal value. This currency will override any existing currency for the decimal.

decimalValue

The decimal value to coerce to the specified unit and/or currency.

unitValue

The unit to set for the decimal value. This unit will override any existing unit for the decimal.

CONCATENATE

Returns the string consisting of the concatenated values of all of the strings supplied. None of the string values supplied can be null, or an error will be returned.

RETURNS	FUNCTION SIGNATURES
string	CONCATENATE(string[...] strings) Accepts a LIST of strings to concatenate. All elements in the list must be non-null.
string	CONCATENATE(string firstString, [string otherStrings]...)

RETURNS	FUNCTION SIGNATURES
string	CONCATENATE(NullIndicator null_handling_behavior, string[...] strings) Accepts a LIST of strings to concatenate. All elements in the list must be non-null.
string	CONCATENATE(NullIndicator null_handling_behavior, @Nullable string firstString, [@Nullable string otherStrings]...)

Input Arguments:

firstString
The first string in the concatenated result.
null_handling_behavior (keyword type: NullIndicator) Allowed values: IGNORE_NULL
The keyword that determines the intended handling of nulls. When set to IGNORE_NULL, any null input strings
are skipped.
otherStrings
Additional strings to concatenate onto the result.
strings
List of strings to concatenate in order.

CONST

Retrieves the values of commonly used constants (E or PI).

RETURNS	FUNCTION SIGNATURES
decimal	CONST(ConstantKeyword keyword)

Input Arguments:

keyword (keyword type: ConstantKeyword)
Allowed values: PI, E
The name of the built-in constant to return. Supported constants are: E, and PI.

CONTAINS

Checks whether a list contains a specific value.

BOOLEAN	CONTAINS(@NULLABLE BOOLEAN VALUE, BOOLEAN[...] VALUELIST) RETURNS TRUE IF THE PROVIDED LIST CONTAINS THE SPECIFIED VALUE; OTHERWISE FALSE. THE LIST MAY CONTAIN NULL VALUES; SEARCHING FOR NULL WILL RETURN TRUE IF THE LIST CONTAINS AT LEAST ONE NULL ELEMENT. THE LIST ARGUMENT ITSELF MUST NOT BE NULL.
boolean	CONTAINS(@Nullable currency value, currency[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable date value, date[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable datetime value, datetime[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable decimal value, decimal[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable dictionary value, dictionary[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable string value, string[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.
boolean	CONTAINS(@Nullable unit value, unit[...] valueList) Returns TRUE if the provided list contains the specified value; otherwise FALSE. The list may contain null values; searching for NULL will return TRUE if the list contains at least one null element. The list argument itself must not be null.

Input Arguments:

value
The value to search for within the list. May be NULL to search for null entries.

valueList

A LIST(...) of values to search. The list may contain NULL values, but the list itself must not be null.

CONVERT

Converts the supplied decimal value into the provided currency and / or unit if a conversion is available. If a conversion factor between the existing currency or unit does not exist, then an error is generated. If there is an exponent specified for the value, the exponent will be taken into account as

part of the conversion. For example, CONVERT(USD, 10 EUR^2) will convert to USD^2 by applying the conversion factor twice. If the supplied decimal value is null, the returned value is null.

DECIMAL	CONVERT(CURRENCY CURRENCYVALUE, @NULLABLE DECIMAL DECIMALVALUE)
decimal	CONVERT(unit unitValue, @Nullable decimal decimalValue)
decimal	CONVERT(currency currencyValue, unit unitValue, @Nullable decimal decimalValue)

Input Arguments:

currencyValue
The currency to set for the decimal value. If no conversion from the current currency exists, then the

function will generate an error.

decimalValue

The decimal value to convert to the specified unit and / or currency.

unitValue

The unit to convert to for the decimal value. If no conversion from the current unit exists, then the function will generate an error.

COS

Computes the cosine of a scalar angle given in radians. If your input is in degrees, see the RADIANS function.

Input Arguments:

list
list containing zero or more entries

COUNT

Counts the number of entries in a list of values, returns the size of the list.

Input Arguments:

radians
Scalar angle measured in radians.

CURRENCY

Returns a CURRENCY type from the input. The CURRENCY type is a "first class" type in the formula engine and can be the result of an element formula or other calculation.

CURRENCY(DECIMAL DECIMALVALUE) EXTRACTS THE CURRENCY FROM THE GIVEN EXPRESSION. THE EXPRESSION MUST RESULT IN A VALID, NON-CURRENCY	NULL VALUE. IF THE EXPRESSION HAS NO ASSOCIATED CURRENCY, RETURNS A SPECIAL VALUE REPRESENTING THE ABSENCE OF A CURRENCY.
currency	CURRENCY(@Nullable string currencyCode) Returns a CURRENCY type with the given currency code. The leading and trailing whitespace in the currency code will be trimmed. An empty or whitespace only currency code will return a special value representing the absence of a currency.

Input Arguments:

currencyCode
The string representation of the currency to create.

decimalValue

The decimal value from which to extract the associated currency.

CURRENCY PER QTY

Returns a CURRENCY PER QTY type from the input. The CURRENCY PER QTY type can be the result of an element formula or other calculation.

CURRENCY_PER_QTY	CURRENCY_PER_QTY(STRING CURRENCY, STRING UNIT) RETURNS A CURRENCY_PER_QUANTITY VALUE WITH THE GIVEN CURRENCY CODE AND THE GIVEN UNIT CODE. ASSOCIATED QUANTITY WILL BE DEFAULTED TO 1. THE LEADING AND TRAILING WHITESPACE IN THE CURRENCY AND UNIT CODES WILL BE TRIMMED. AN EMPTY OR WHITESPACE ONLY CURRENCY OR UNIT CODES WILL RETURN A SPECIAL VALUE REPRESENTING THE ABSENCE OF A CURRENCY OR A UNIT.
currency_per_qty	CURRENCY_PER_QTY(currency currencyValue, unit unitValue) Returns a CURRENCY_PER_QUANTITY value with the given currency and the given unit. Associated quantity will be defaulted to 1.
currency_per_qty	CURRENCY_PER_QTY(decimal monetaryPerQuantity) RReturns a CURRENCY_PER_QUANTITY value from the given MONETARY_PER_QUANTITY value.
currency_per_qty	CURRENCY_PER_QTY(currency currencyValue, unit unitValue, decimal perQty) Returns a CURRENCY_PER_QUANTITY value with the given currency, the given unit and the given quantity.

Input Arguments:

currency
The string representation of the currency to create.

unit

The string representation of the unit to create.

currencyValue

The currency value to use.

unitValue

The unit value to use.

perQty

The quantity value to use.

DATE

Generates new date values from various inputs. The resulting date is not aware of time zones.

DATE	DATE(DATETIME DATETIMEVALUE) CONVERTS A DATE-TIME TO A DATE. THE TIME PORTION OF THE INPUT DATE-TIME WILL BE IGNORED.
date	DATE(string dateAsString) Parses a string to a date value. The string is required to be in one of the following formats: YYYY-MM-DD, similar to ISO8601 for date YYYY-MM-DDThh:mm:ss, similar to ISO8601 for date and time; the time portion will be ignored YYYYMMDD for date YYYYMMDDhhmm, the hour and minute will be ignored YYYYMMDDhhmmss, the hour, minute, and second will be ignored.

Input Arguments:

dateAsString
The string to convert to a date.

dateTimeValue

The date-time value to convert to a date by removing the time components.

DATEDIFF

Calculates the specified difference between two dates for the given interval. calculates the number of complete intervals between the start date and the end date.

DECIMAL	DATEDIFF(INTERVAL INTERVAL, DATE STARTDATE, DATE ENDDATE, STRING TIMEZONE)
decimal	DATEDIFF(Interval interval, datetime startDateTime, datetime endDateTime, string timeZone)

Input Arguments:

endDate
the end date

endDateTime

the end date and time
interval (keyword type: Interval)
Allowed values: YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
Interval of time to calculate between start and end date
startDate
the start date
startDateTime
the start date and time
timeZone
time zone identifier. Can be of the following formats:
- "Z","GMT", "UTC" or "UT" which corresponds to UTC
- Begin with "+" or "-" followed by the UTC offset (format "h", "hh:mm", hhhh:mm:ss, etc.)
- "GMT", "UTC" or "UT" followed by a "+" or "-" and a UTC offset
- Region id of format "{area/city}" such as "Europe/Paris"

DATETIME

Generates new date-time values from various inputs. The resulting date-time is not aware of time zones.

DATETIME	DATETIME(DATE DATEVALUE) CONVERTS A DATE TO A DATE-TIME. THE TIME WILL BE SET TO "00:00:00".
datetime	DATETIME(string dateTimeAsString) Parses a string to a date-time value. The string is required to be in one of the following formats: YYYY-MM-DD, similar to ISO8601 for date; the time will be set to "00:00:00" YYYY-MM-DDThh:mm:ss, similar to ISO8601 for date and time YYYYMMDD for date; the time will be set to "00:00:00" YYYYMMDDhhmm for date and time up to the minute; seconds will be set to :00 YYYYMMDDhhmmss for date and time
datetime	DATETIME(date dateSource, datetime timeSource) Combines a date from the first input and the time from the second input.
datetime	DATETIME(datetime dateSource, datetime timeSource) Combines a date from the first input and the time from the second input.

Input Arguments:

dateSource
Date or date-time value that will provide the date of the generated value.

dateTimeAsString

The string representation of the date or datetime.

dateValue

The date to convert to a datetime by setting the time to "00:00:00".

timeSource

Date-time value that will provide the time of the generated value; this value's date will be ignored.

DAYS_BETWEEN

Returns the number of days between two dates, two date times, a date and date time, or a date time and date. The returned result will always be an integer (could be zero, positive, or negative) representing the number of complete days between the start (inclusive) and end (exclusive).

DECIMAL	DAYS_BETWEEN(DATE STARTDATE, DATE ENDDATE)
decimal	DAYS_BETWEEN(date startDate, datetime endDateTime)
decimal	DAYS_BETWEEN(datetime startDateTime, date endDate)
decimal	DAYS_BETWEEN(datetime startDateTime, datetime endDateTime)

Input Arguments:

endDate
The end date for the calculation. The time is set to midnight in order to calculate the time difference.

endDateTime

The end date and time for the calculation.

startDate

The start date for the calculation. The time is set to midnight in order to calculate the time difference.

startDateTime

The start date and time for the calculation.

DAY_OF

Computes a date relative to an input date and some adjacency or windowed boundary condition. Boundary conditions, as an example, could be the start or end date of a month or quarter, or the Nth Friday (or other day-of-week) within the same. When targeting a particular day-of-week, it also allows finding the nearest, previous, or next occurrence. The output is always a date, without time, and the input must not be null.

DATE	DAY_OF(ADJACENCY ADJACENCY, DAYOFWEEK DAYOFWEEK, DATE DATE) COMPUTES A SPECIFIC DAY-OF-WEEK IN PROXIMITY TO THE INPUT DATE. EXAMPLES: DAY_OF(NEAREST, FRIDAY, #2022-04-08#) = #2022-04-08# DAY_OF(PREVIOUS, FRIDAY, #2022-04-08#) = #2022-04-01# DAY_OF(NEXT, FRIDAY, #2022-04-08#) = #2022-04-15# DAY_OF(NEAREST, MONDAY, #2022-04-08#) = #2022-04-11# DAY_OF(NEAREST, TUESDAY, #2022-04-08#) = #2022-04-05#
date	DAY_OF(Adjacency adjacency, DayOfWeek dayOfWeek, datetime dateTime) Equivalent to: DAY_OF(adjacency, dayOfWeek, DATE(dateTime))
date	DAY_OF(DateWindow window, Terminus terminus, date date) Computes the starting or ending day of the boundary window, for any day-of-week. Examples: DAY_OF(QUARTER, START, #2020-05-15#) = #2020-04-01# DAY_OF(QUARTER, END, #2020-05-15#) = #2020-06-30# DAY_OF(MONTH, START, #2020-05-15#) = #2020-05-01# DAY_OF(MONTH, END, #2020-05-15#) = #2020-05-31# DAY_OF(FEB, START, #2020-05-15#) = #2020-02-01# DAY_OF(FEB, END, #2021-05-15#) = #2021-02-28# DAY_OF(FEB, END, #2020-05-15#) = #2020-02-29#
date	DAY_OF(DateWindow window, Terminus terminus, datetime dateTime) Equivalent to: DAY_OF(window, terminus, DATE(dateTime))
date	DAY_OF(DateWindow window, Occurrence occurrence, DayOfWeek dayOfWeek, date date) Computes one of several occurrences of a specific day-of-week within the boundary month or quarter. Examples: DAY_OF(QUARTER, FIRST, FRIDAY, #2020-05-15#) = #2020-04-03# DAY_OF(QUARTER, THIRD, FRIDAY, #2020-05-15#) = #2020-04-17# DAY_OF(QUARTER, LAST, FRIDAY, #2020-05-15#) = #2020-06-26# DAY_OF(MONTH, FIRST, THURSDAY, #2020-05-15#) = #2020-05-07# DAY_OF(MONTH, SECOND, THURSDAY, #2020-05-15#) = #2020-05-14# DAY_OF(MONTH, LAST, THURSDAY, #2020-05-15#) = #2020-05-28# DAY_OF(SEP, FIRST, WEDNESDAY, #2020-05-15#) = #2020-09-02# DAY_OF(SEP, FOURTH, WEDNESDAY, #2020-05-15#) = #2020-09-23# DAY_OF(SEP, LAST, WEDNESDAY, #2020-05-15#) = #2020-09-30#
date	DAY_OF(DateWindow window, Occurrence occurrence, DayOfWeek dayOfWeek, datetime dateTime) Equivalent to: DAY_OF(window, occurrence, dayOfWeek, DATE(dateTime))

Input Arguments:

adjacency (keyword type: Adjacency) Allowed values: PREVIOUS, NEAREST, NEXT
Directs the search for a nearby date with the requested day-of-week.
- NEAREST finds the closest matching value in either direction, which can be the input date
- NEXT finds the value occurring strictly after the input date
- PREVIOUS finds the value occurring strictly before the input date
  date
  Determines the year and, in some cases, the month used as the boundary window.
  dateTime
  Determines the year and, in some cases, the month used as the boundary window. The time value of this input
  is ignored.
dayOfWeek (keyword type: DayOfWeek)
Allowed values: MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY
One of the seven days of a standard Gregorian calendar week.
occurrence (keyword type: Occurrence)
Allowed values: FIRST, SECOND, THIRD, FOURTH, LAST
Selects the FIRST, SECOND, THIRD, FOURTH, or LAST occurrence of the desired day-of-week within the boundary
window.
terminus (keyword type: Terminus) Allowed values: START, END
Selects the START or END day within the boundary window.
window (keyword type: DateWindow)
Allowed values: QUARTER, MONTH, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
Selects the desired boundary window. MONTH and QUARTER generate dates based upon the year and month of the
input, while JAN-DEC values generate dates in their respective months within the same calendar year as the
input.

DECIMAL

Converts from a string representation of a decimal number into decimal type. The string representation consists of an optional sign, '+' ( '+') or '-' ('-'), followed by a sequence of zero or more decimal digits ("the integer"), optionally followed by a fraction, optionally followed by an exponent.

DECIMAL	DECIMAL(STRING STRINGEXPRESSION) EVALUATE THE STRING VALUED EXPRESSION AND CONVERT IT TO DECIMAL.
decimal	DECIMAL(@Nullable string stringExpression, decimal defaultDecimalExpression) Evaluate the string valued expression and convert it to decimal. If the string is null or there is an error during the evaluation of the string expression, the default value is returned.

Input Arguments:

defaultDecimalExpression
decimal valued expression to evaluate as the default value.

stringExpression

string valued expression to evaluate and convert to decimal.

DEGREES

Converts a scalar value in radians to degrees.

Input Arguments:

radians
Scalar angle given in radians.

ERROR_IF_NULL

Generates an error with a custom error message when the input expression evaluates to null. An error will stop further computation of the current element, unless this function expression is used within TRYINORDER; in that case, the TRYINORDER will attempt its next expression, if any. If the input expression is non-null, it is used as the function result.

BOOLEAN	ERROR_IF_NULL(@NULLABLE BOOLEAN EXPRESSION, STRING MESSAGE)
currency	ERROR_IF_NULL(@Nullable currency expression, string message)
date	ERROR_IF_NULL(@Nullable date expression, string message)
datetime	ERROR_IF_NULL(@Nullable datetime expression, string message)
decimal	ERROR_IF_NULL(@Nullable decimal expression, string message)
string	ERROR_IF_NULL(@Nullable string expression, string message)
unit	ERROR_IF_NULL(@Nullable unit expression, string message)

Input Arguments:

expression
The expression to be evaluated, and should have the potential to be null. To force an error, use the NULL

function as the expression.

message

Custom error message that would be given when the expression evaluates to null.

FIND

Search for a substring within the provided text, and return the location in the string where it is found, or null if it is not found. If the starting position of the search in the string is not specified, it defaults to the beginning of the string.

DECIMAL	FIND(STRING FINDTEXT, STRING WITHINTEXT)
decimal	FIND(string findText, string withinText, decimal start)

Input Arguments:

findText

The text to find.

start

The starting position of the search in the text. Defaults to 1 if not specified.

withinText

The text to search for the text to find.

FIND_LAST

Search for a substring backwards within the provided text, and return the last location in the string where it is found, or null if it is not found. If the starting position of the search in the string is not specified, it defaults to the end of the string.

DECIMAL	FIND_LAST(STRING FINDTEXT, STRING WITHINTEXT)
decimal	FIND_LAST(string findText, string withinText, decimal start)

Input Arguments:

findText

The text to find.

start

The starting position of the search in the text. Defaults to the end of the string to search if not specified.

withinText

The text to search for the text to find.

IF

Returns one of two values based on the truth value of the boolean expression in the first parameter. The two values must have the same data type. The second parameter is returned if the boolean expression is true and the third parameter is returned if the boolean expression is false. Only the parameter actually returned is evaluated.

BOOLEAN	IF(BOOLEAN BOOLEANEXPRESSION, @NULLABLE BOOLEAN VALUEIFTRUE, @NULLABLE BOOLEAN VALUEIFFALSE)
currency	IF(boolean booleanExpression, @Nullable currency valueIfTrue, @Nullable currency valueIfFalse)
date	IF(boolean booleanExpression, @Nullable date valueIfTrue, @Nullable date valueIfFalse)
datetime	IF(boolean booleanExpression, @Nullable datetime valueIfTrue, @Nullable datetime valueIfFalse)
decimal	IF(boolean booleanExpression, @Nullable decimal valueIfTrue, @Nullable decimal valueIfFalse)
string	IF(boolean booleanExpression, @Nullable string valueIfTrue, @Nullable string valueIfFalse)
unit	IF(boolean booleanExpression, @Nullable unit valueIfTrue, @Nullable unit valueIfFalse)

Input Arguments:

booleanExpression
A boolean valued expression used to decide which parameter is returned.

valueIfFalse

The value that is returned if the condition expression evaluates to false.

valueIfTrue

The value that is returned if the condition expression evaluates to true.

IS_BETWEEN

Returns true if the specified value is between the start and end values, inclusive. Returns false if the value falls outside of that range. The start value must be less than or equal to the end value, and none of the values can be null.

BOOLEAN	IS_BETWEEN(DATE VALUE, DATE START, DATE END)
boolean	IS_BETWEEN(datetime value, datetime start, datetime end)
boolean	IS_BETWEEN(decimal value, decimal start, decimal end)

Input Arguments:

end
The largest value in the range to test, cannot be null.

start

The smallest value in the range to test, cannot be null.

value

The value to test, cannot be null.

IS_NULL

Returns true if the argument value is null and false if the argument value is not null.

BOOLEAN	IS_NULL(@NULLABLE ANY VALUE)
boolean	IS_NULL(@Nullable any[...] list) Indicates whether the list is itself null. Does not examine its contents.

Input Arguments:

list

Any kind of list.

value

Any kind of value to check for null.

JOIN

Concatenates multiple strings with a specified delimiter. The delimiter string may be empty, but it cannot be null.

JOIN(STRING DELIMITER_STRING, NULLINDICATOR NULL_HANDLING_BEHAVIOR, [@NULLABLE STRING VALUES]...) THIS OVERLOAD OMITS ANY INPUT STRINGS THAT ARE NULL, AS IF THEY HAD NOT BEEN PASSED AS ARGUMENTS TO THE FUNCTION. STRING	EXAMPLES: JOINS THE LIST OF STRINGS "A", "B", "C" TOGETHER SEPARATED BY THE DELIMITER "," JOIN(",", IGNORE_NULL, "A", "B", "C") = "A,B,C" SKIP ANY NULLS IN THE LIST AS WELL AS THE DELIMITER THAT WOULD BE OUTPUT WITH THE VALUE. JOIN(",", IGNORE_NULL, "A", NULL(""), "C") = "A,C" WHEN ALL THE PROVIDED STRINGS ARE NULL, RETURN NULL. JOIN(",", IGNORE_NULL, NULL(""), NULL("")) = NULL
string	JOIN(string delimiter_string, string replacement_string, NullBehavior null_handling_behavior, [@Nullable string values]...) This overload replaces any input strings that are null with a replacement string, which itself may be empty but not null. Examples: The two overloads behave the same when there are no nulls in the list. JOIN(",", "replace", REPLACE_NULL, "a", "b", "c") = "a,b,c" Any nulls in the list of strings are replaced with the specified replacement string. JOIN(",", "replace", REPLACE_NULL, "a", NULL(""), "c") = "a,replace,c" The replacement string can be the empty string. JOIN(",", "", REPLACE_NULL, "a", NULL(""), "c") = "a,,c" If no strings are provided, the return value is null. JOIN(",", "", REPLACE_NULL) = null

JOIN(STRING DELIMITER_STRING, NULLINDICATOR NULL_HANDLING_BEHAVIOR, [@NULLABLE STRING VALUES]...) THIS OVERLOAD OMITS ANY INPUT STRINGS THAT ARE NULL, AS IF THEY HAD NOT BEEN PASSED AS ARGUMENTS TO THE FUNCTION. STRING

EXAMPLES: JOINS THE LIST OF STRINGS "A", "B", "C" TOGETHER SEPARATED BY THE DELIMITER "," JOIN(",", IGNORE_NULL, "A", "B", "C") = "A,B,C" SKIP ANY NULLS IN THE LIST AS WELL AS THE DELIMITER THAT WOULD BE OUTPUT WITH THE VALUE. JOIN(",", IGNORE_NULL, "A", NULL(""), "C") = "A,C" WHEN ALL THE PROVIDED STRINGS ARE NULL, RETURN NULL. JOIN(",", IGNORE_NULL, NULL(""), NULL("")) = NULL

string

JOIN(string delimiter_string, string replacement_string, NullBehavior null_handling_behavior, [@Nullable string values]...) This overload replaces any input strings that are null with a replacement string, which itself may be empty but not null. Examples: The two overloads behave the same when there are no nulls in the list. JOIN(",", "replace", REPLACE_NULL, "a", "b", "c") = "a,b,c" Any nulls in the list of strings are replaced with the specified replacement string. JOIN(",", "replace", REPLACE_NULL, "a", NULL(""), "c") = "a,replace,c" The replacement string can be the empty string. JOIN(",", "", REPLACE_NULL, "a", NULL(""), "c") = "a,,c" If no strings are provided, the return value is null. JOIN(",", "", REPLACE_NULL) = null

Input Arguments:

delimiter_string
The non-null delimiter string placed between adjacent values.
null_handling_behavior (keyword types: NullIndicator, NullBehavior) Allowed values: IGNORE_NULL, REPLACE_NULL
The keyword that determines the intended handling of nulls.
replacement_string
Non-null string that will appear in place of any null input values.

values

One or more string values. If none are provided, this function always returns null.

LEFT

Returns the first character or characters in a text string, based on the specified size.

Input Arguments:

length
The number of characters to return.

value

The string value from which to return characters.

LENGTH

Count the number of characters in the specified string

Input Arguments:

value
The string being measured for length

LIST

Creates a list from multiple individual values. All values within the list must be uniformly the same data type. To create an empty list, use the IGNORE_NULL keyword with exactly one NULL(...) value. This function does not support creating lists of lists.

BOOLEAN[...]	LIST(@NULLABLE BOOLEAN FIRST, [@NULLABLE BOOLEAN REST]...)
boolean[...]	LIST(NullIndicator ignoreNull, @Nullable boolean first, [@Nullable boolean rest]...)
currency[...]	LIST(@Nullable currency first, [@Nullable currency rest]...)
currency[...]	LIST(NullIndicator ignoreNull, @Nullable currency first, [@Nullable currency rest]...)
date[...]	LIST(@Nullable date first, [@Nullable date rest]...)
date[...]	LIST(NullIndicator ignoreNull, @Nullable date first, [@Nullable date rest]...)
datetime[...]	LIST(@Nullable datetime first, [@Nullable datetime rest]...)
datetime[...]	LIST(NullIndicator ignoreNull, @Nullable datetime first, [@Nullable datetime rest]...)
decimal[...]	LIST(@Nullable decimal first, [@Nullable decimal rest]...)
decimal[...]	LIST(NullIndicator ignoreNull, @Nullable decimal first, [@Nullable decimal rest]...)
string[...]	LIST(@Nullable string first, [@Nullable string rest]...)
string[...]	LIST(NullIndicator ignoreNull, @Nullable string first, [@Nullable string rest]...)
unit[...]	LIST(@Nullable unit first, [@Nullable unit rest]...)
unit[...]	LIST(NullIndicator ignoreNull, @Nullable unit first, [@Nullable unit rest]...)

Input Arguments:

first
first item in the generated list
ignoreNull (keyword type: NullIndicator)
Allowed values: IGNORE_NULL
excludes null values (first itself or within rest) when set to IGNORE_NULL
rest
zero or more additional items to follow the first

LN

Computes the natural logarithm of the input given in decimal.

Input Arguments:

value
Scalar decimal, must be non-null and greater than zero.

LOG

Computes the base 10 logarithm of the input given in decimal.

Input Arguments:

value
Scalar decimal, must be non-null and greater than zero.

LOWER

Applies case-folding rules to convert the input string to lowercase.

STRING	LOWER(@NULLABLE STRING VALUE) APPLIES DEFAULT CASING RULES WITHOUT SPECIAL LANGUAGE SUPPORT.
string	LOWER(@Nullable string value, @Nullable string languageTag) Case-folding rules and therefore the resulting output may vary by language, such as German, Turkish, and others. Use this signature to apply rules for a specific language.

Input Arguments:

languageTag
The language tag representing the locale to use to convert the string to lower case, in IETF BCP 47 language

tag format ("en-US", "en", "jp-JP"). If the language tag is null, invalid, the special value "und", or the

special value "zxx", then default casing rules without special language support are used instead.

value

The string to be converted. If null, the result of the function will be null.

LTRIM

Accepts a string and removes the leading whitespaces. If the input string is null, the output is also null. Characters are considered whitespace according to the Unicode standard, excluding non-breaking spaces, and also includes line endings and certain rarely-used record separator characters.

Input Arguments:

toTrim
The string to be trimmed.

MATCH

Returns the position of the value specified by the key in the list of provided values. If the match is not unique in the list, the function will return the first match it finds in the list (short-circuit behavior). If no match is found, the function will return "null". The key and all values in the list must be of the same type. After the key parameter, the following values are numbered sequentially, starting with one. Do not specify null for the key.

DECIMAL	MATCH(BOOLEAN KEY, @NULLABLE BOOLEAN FIRSTVALUE, [@NULLABLE BOOLEAN OTHERVALUES]...) EXAMPLES: MATCH(FALSE, TRUE, FALSE, TRUE, FALSE) = 2
decimal	MATCH(currency key, @Nullable currency firstValue, [@Nullable currency otherValues]...)
decimal	MATCH(date key, @Nullable date firstValue, [@Nullable date otherValues]...)
decimal	MATCH(datetime key, @Nullable datetime firstValue, [@Nullable datetime otherValues]...)
decimal	MATCH(decimal key, @Nullable decimal firstValue, [@Nullable decimal otherValues]...) Examples: MATCH(7, 9, 8, 7, 6) = 3
decimal	MATCH(string key, @Nullable string firstValue, [@Nullable string otherValues]...) Examples: MATCH("One", "Two", "Three", "One", "Four") = 3
decimal	MATCH(unit key, @Nullable unit firstValue, [@Nullable unit otherValues]...)

Input Arguments:

firstValue
The first key comparison value, at position one.

key

The value to match in list of values

otherValues

Additional values to compare with key

MAX

Returns the largest item in a list of two or more items. "Largest" may be defined differently depending on the types of items being compared.

DECIMAL	MAX(DECIMAL[...] NUMBERS) RETURNS THE LARGEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.
decimal	MAX(decimal firstNumber, [decimal otherNumbers]...) Returns the largest number from the list of numbers. All numbers provided must have the same units and / or currency, and must also not be null. If the numbers do not meet both conditions, the evaluation will result in an error.
decimal	MAX(NullIndicator behaviorForNulls, decimal[...] numbers) Returns the largest number from the provided LIST of numbers, while ignoring any null values. If all of the numbers provided in the LIST are null, the function returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

MAX(DECIMAL[...] NUMBERS) RETURNS THE LARGEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST DECIMAL	HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.
decimal	MAX(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumbers]...) Returns the largest number from the list of numbers, while ignoring any nulls that may be present in the list. All numbers provided must have the same units and / or currency, otherwise evaluation will result in an error. If all numbers provided are null, then this function returns null as well.

MAX(DECIMAL[...] NUMBERS) RETURNS THE LARGEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST DECIMAL

HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.

decimal

MAX(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumbers]...) Returns the largest number from the list of numbers, while ignoring any nulls that may be present in the list. All numbers provided must have the same units and / or currency, otherwise evaluation will result in an error. If all numbers provided are null, then this function returns null as well.

Input Arguments:

behaviorForNulls (keyword type: NullIndicator) Allowed values: IGNORE_NULL
Use the IGNORE_NULL keyword to accept (and ignore) null values instead of generating an error. Currently,
IGNORE_NULL is the only legal value.
firstNumber
The first number to compare, must not be null unless IGNORE_NULL is also specified.
numbers
A LIST of numbers to compare. All numbers must have the same units and / or currency. Nulls are not allowed
unless IGNORE_NULL is specified.
otherNumbers
The subsequent number(s) to compare to the first number, must have the same units and currency as the first
number, and must not be null unless IGNORE_NULL is also specified.

MID

Returns the specified number of characters from a text string starting at the specified position in the string.

Input Arguments:

length
The number of characters to return from the value starting at start.

start

The starting position in the value.

value

The string value from which to return characters.

MIN

Returns the smallest item in a list of two or more items. "Smallest" may be defined differently depending on the types of items being compared.

MIN(DECIMAL[...] NUMBERS) RETURNS THE SMALLEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST DECIMAL	HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.
decimal	MIN(decimal firstNumber, [decimal otherNumber]...) Returns the smallest number from the list of numbers. All numbers provided must have the same units and / or currency, and must also not be null. If the numbers do not meet both conditions, the evaluation will result in an error.

DECIMAL	MIN(DECIMAL[...] NUMBERS) RETURNS THE SMALLEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.
decimal	MIN(NullIndicator behaviorForNulls, decimal[...] numbers) Returns the smallest number from the provided LIST of numbers, while ignoring any null values. If all of the numbers provided in the LIST are null, the function returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.
decimal	MIN(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumber]...) Returns the smallest number from the list of numbers, while ignoring any nulls that may be present in the list. All numbers provided must have the same units and / or currency, otherwise evaluation will result in an error. If all numbers provided are null, then this function returns null as well.

DECIMAL

MIN(DECIMAL[...] NUMBERS) RETURNS THE SMALLEST NUMBER FROM THE PROVIDED LIST OF NUMBERS. ALL NUMBERS PROVIDED MUST HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR.

decimal

MIN(NullIndicator behaviorForNulls, decimal[...] numbers) Returns the smallest number from the provided LIST of numbers, while ignoring any null values. If all of the numbers provided in the LIST are null, the function returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

decimal

MIN(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumber]...) Returns the smallest number from the list of numbers, while ignoring any nulls that may be present in the list. All numbers provided must have the same units and / or currency, otherwise evaluation will result in an error. If all numbers provided are null, then this function returns null as well.

Input Arguments:

behaviorForNulls (keyword type: NullIndicator) Allowed values: IGNORE_NULL
Use the IGNORE_NULL keyword to accept (and ignore) null values instead of generating an error. Currently,
IGNORE_NULL is the only legal value.
firstNumber
The first number to compare, must be non-null unless IGNORE_NULL is also specified.
numbers
A LIST of numbers to compare. All numbers must have the same units and / or currency. Nulls are not allowed
unless IGNORE_NULL is specified.
otherNumber
The subsequent number(s) to compare to the first number, must have the same units and currency as the first
number, and must not be null unless IGNORE_NULL is also specified.

MROUND

Rounds an input value to the nearest multiple of a given scalar factor. If a value is equidistant from the two nearest multiples, the function will choose the one farthest away from zero(0). That is the higher multiple when the input is positive and the lower multiple when the input is negative. The currency and/or unit of the input value will be preserved as-is; only the scalar component of the input value is affected.

Input Arguments:
- multiple
  Defines the target rounding multiple. Must be a scalar (no currency or unit), must not be negative, and must
  
  not be null. A multiple of zero(0) will yield a zero(0) result for non-null inputs.
  
  number
  
  Value to be rounded. If null, the result of the function will also be null.

NORMAL_DIST

Returns the probability density or cumulative probability for a normal distribution given the type of calculation (DENSITY or CUMULATIVE), the mean, standard deviation,and x value.

Input Arguments:
- form (keyword type: Form)
  Allowed values: DENSITY, CUMULATIVE
  The form of the normal distribution calculation to compute. Must be DENSITY or CUMULATIVE./
  mean
  The mean of the normal distribution.
  standardDeviation
  The standard deviation of the normal distribution.
  xValue
  X value to evaluate for the given normal distribution calculation.

NULL

Always returns a null (missing) value. The input value or expression is not used except to define the function's return data type.

BOOLEAN	NULL(@NULLABLE BOOLEAN TYPEVALUE)
currency	NULL(@Nullable currency typeValue)
date	NULL(@Nullable date typeValue)
datetime	NULL(@Nullable datetime typeValue)
decimal	NULL(@Nullable decimal typeValue)
string	NULL(@Nullable string typeValue)
unit	NULL(@Nullable unit typeValue)

Input Arguments:

typeValue
Assigns the data type of the generated null to match this input value type. A constant value is recommended;

this parameter is discarded, ignored, and never evaluated.

POWER

Raises a base value to a power or computes its root given an exponent. For example, the square of three is POWER(3, 2) = 9, and the square-root of four is POWER(4, 1/2) = 2. This operation may result in the loss of some numerical accuracy under certain circumstances, such as when the exponent is fractional (not an integer).

Input Arguments:

base
The value to raise (or lower) to an exponential power (or root). Any currency and/or unit on this value will

carry through to the result. Negative values are supported but must be used only with an exponent whose

denominator is odd. (Even-valued roots of negative base values cannot be represented as real numbers.)

exponent

A scalar exponent to which the base value will be raised (or lowered). Use 2 for square, 3 for cube, etc.

Square root is 1/2. Cube root is 1/3. A value such as 5/3 can be read "compute the cube root raised

to the fifth power". Exponents with very large absolute values (e.g. one billion), and exponents with an

associated currency and/or unit, are not supported.

RADIANS

Converts a scalar value in degrees to radians. Can be used as RADIANS(180) to obtain an approximation for pi.

Input Arguments:

degrees
Scalar angle given in degrees.

RANK

Returns the nth largest value in the list of double values provided as parameters to the function. The list of values provided is sorted in descending order and the value in the list at the ordinal index is returned. If the specified ordinal value is larger than the number of items in the resulting list, then the function returns the last item in the list.

DECIMAL	RANK(DECIMAL ORDINAL, [DECIMAL VALUELIST]...)
decimal	RANK(NullIndicator behaviorForNulls, decimal ordinal, [@Nullable decimal valueList]...) Null values are ignored while sorting and processing the list.

Input Arguments:

behaviorForNulls (keyword type: NullIndicator) Allowed values: IGNORE_NULL
Use the IGNORE_NULL keyword to accept (and ignore) null values instead of generating an error. Currently,
IGNORE_NULL is the only legal value.
ordinal
The ordinal value of the index in the sorted list to return. The value must be a non-negative integer, and
must not have currency and unit values.
valueList
The list of double valued expressions to evaluate and sort.

REPLACE

Finds a specified search string within the original value and replaces each occurence with a substitute value. A null original value will generate a null result.

Input Arguments:

original
The original value to modify

toFind

Parts of the the original value matching this substring will be replaced by the toReplace value.

Empty

strings not accepted.

toReplace

Every occurence of toFind in the original input will be replaced by this value.

RIGHT

Returns the last character or characters in a text string, based on the specified size.

Input Arguments:

length
The number of characters to return from the end of the string.

value

The string value from which to return characters.

ROUND

Rounds an input value to a number of fractional digits.

DECIMAL	ROUND(@NULLABLE DECIMAL INPUT) ROUNDS THE INPUT VALUE (X) TO AN INTEGER, AS IF WRITTEN AS ROUND(X, 0).
decimal	ROUND(@Nullable decimal input, decimal scale) Rounds the input value to the number of fractional digits indicated by the scale parameter, using the HALF_UP rounding mode. If the digit to the right of the rounding
decimal	ROUND(@Nullable decimal input, decimal scale, RoundMode mode) Rounds the input value to the number of fractional digits indicated by the scale parameter, using the specified rounding mode.

Input Arguments:

input
Value to be rounded. If null, the result of the function will also be null.
mode (keyword type: RoundMode)
Allowed values: UP, DOWN, CEILING, FLOOR, HALF_UP, HALF_DOWN, HALF_EVEN
Selects the rounding behavior, which applies when the input value must be modified in order to satisfy the
requested number of fractional digits. The supported modes are: UP always adjusts away from zero;
DOWN always adjusts toward zero; CEILING never decreases;
FLOOR never increases;
HALF_UP rounds away from zero when the digit to the right of the rounding position is ≥ 5; HALF_DOWN rounds toward zero when the digit to the right of the rounding position is ≤ 5; HALF_EVEN (banker's rounding) rounds to the nearest neighbor unless they are equidistant, in which case it
rounds to the even neighbor.
scale
Number of desired fractional digits to the right of the decimal point. This value must not be null.

RTRIM

Accepts a string and removes the trailing whitespaces. If the input string is null, the output is also null. Characters are considered whitespace according to the Unicode standard, excluding non-breaking spaces, and also includes line endings and certain rarely-used record separator characters.

Input Arguments:

toTrim
The string to be trimmed.

SCALAR

Preserves only the sign and magnitude of the input value, ignoring currency and/or unit information. This does not perform currency or unit conversions, use with care! The numeric value will not be altered but the result will be interpreted without any currency or unit in subsequent operations.

Input Arguments:

value
Any decimal value, with or without a currency and/or unit. A missing (null) value will yield a null result.

SET_TIME

Returns a datetime value with the time set to the specified hours, minutes, and optionally seconds. If seconds are not specified, they will be set to zero. Hours, minutes and seconds are specified as integer values with a 24 hour clock. The legal values for hours are from 0 to 23 inclusive, minutes are from 0 to 59 inclusive, and seconds are from 0 to 59 inclusive. If any of the values are not integers or are out of range, the function will return an error.

DATETIME	SET_TIME(@NULLABLE DATE DATEVALUE, DECIMAL HOURS, DECIMAL MINUTES)
datetime	SET_TIME(@Nullable datetime dateTimeValue, decimal hours, decimal minutes)
datetime	SET_TIME(@Nullable date dateValue, decimal hours, decimal minutes, decimal seconds)
datetime	SET_TIME(@Nullable datetime dateTimeValue, decimal hours, decimal minutes, decimal seconds)

Input Arguments:

dateTimeValue
The datetime value to use as the basis for setting the time. The time value for this datetime will be overwritten by the time value specified in the function.

dateValue

The date value to use as the basis for setting the time.

hours

The hours to set in the time. The value must be an integer value between 0 and 23, inclusive.

minutes

The minutes to set in the time. The value must be an integer value between 0 and 59, inclusive.

seconds

The seconds to set in the time. The value must be an integer value between 0 and 59, inclusive.

SIGN

Indicates the sign of its input, given as a scalar value: +1 for positive values, 0 for zero, -1 for negative values.

Input Arguments:
- number
  Any numeric value; if null, the result of the function will also be null.

SIN

Computes the sine of a scalar angle given in radians. If your input is in degrees, see the RADIANS function.

Input Arguments:

radians
Scalar angle measured in radians.

SPLIT

Splits the value string on the provided delimiter, and returns the segment of the split string at the provided index. The split string does not include the delimiter. The delimiter can consist of multiple characters, in which case the value string is split wherever the full delimiter string is encountered. If the delimiter is an empty string, then the value string is split between every character. The segment index starts at 1, and if the index is greater than the number of segments generated from the value string, then the result of the function is null.

Input Arguments:

delimiter
The delimiter to use to split the value.

index

Which element of the split string to return.

value

The string to split.

STRING

Converts from various data types to a string representation of that type. Current supported types are decimal (scalar only), date, datetime, currency, and unit.

STRING	STRING(@NULLABLE CURRENCY CURRENCYVALUE)
string	STRING(@Nullable date dateValue)
string	STRING(@Nullable datetime dateTimeValue)
string	STRING(@Nullable decimal scalarDecimalValue)

STRING	STRING(@NULLABLE CURRENCY CURRENCYVALUE)
string	STRING(@Nullable unit unitValue)
string	STRING(@Nullable decimal scalarDecimalValue, @Nullable string languageTag)

Input Arguments:

currencyValue
The currency to convert to a string. The result of the function is the currency code.

dateTimeValue

The datetime to convert to a string. The datetime is converted to ISO 8601 datetime format without timezone information.

dateValue

The date to convert to a string. The date is converted into ISO 8601 date format without timezone information.

languageTag

The language tag representing the locale to use to format the decimal number provided, in IETF BCP 47

language tag format ("en-US", "en", "jp-JP"). If the language tag is null, empty, the special value "und", or

the special value "zxx", then the machine readable format is used instead. If the language tag is ill-formed

according to the standard, then machine readable format is also used.

scalarDecimalValue

The scalar decimal value to convert to a string. A null decimal is converted to a null string. If the decimal

value provided is not a scalar, then the function will return an error.

unitValue

The unit to convert to a string. The result of the function is the unit code.

SUM

Returns the sum of the list of numbers provided as the parameters of the function.

DECIMAL	SUM(DECIMAL[...] DECIMALLIST) RETURNS THE SUM OF THE LIST OF NUMBERS PROVIDED AS THE PARAMETERS OF THE FUNCTION. ALL NUMBERS PROVIDED MUST HAVE THE SAME UNITS AND / OR CURRENCY, AND MUST ALSO NOT BE NULL. IF THE NUMBERS DO NOT MEET BOTH CONDITIONS, THE EVALUATION WILL RESULT IN AN ERROR. THIS OVERLOAD ACCEPTS NUMBERS IN THE FORM OF A LIST TYPE.
decimal	SUM(decimal firstNumber, [decimal otherNumbers]...) Returns the sum of the list of numbers provided as the parameters of the function. All numbers provided must have the same units and / or currency, and must also not be null. If the numbers do not meet both conditions, the evaluation will result in an error.
decimal	SUM(NullIndicator behaviorForNulls, decimal[...] decimalList) Returns the sum of the list of numbers provided as the parameters of the function, while ignoring any null values found. If all of the numbers provided in the list are null, the function also returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error. This overload accepts numbers in the form of a LIST type.
decimal	SUM(NullIndicator behaviorForNulls, @Nullable decimal firstNumber, [@Nullable decimal otherNumbers]...) Returns the sum of the list of numbers provided as the parameters of the function, while ignoring any null values found. If all of the numbers provided in the list are null, the function also returns null. All numbers provided must have the same units and / or currency, otherwise the evaluation will result in an error.

Input Arguments:

behaviorForNulls (keyword type: NullIndicator) Allowed values: IGNORE_NULL
Use the IGNORE_NULL keyword to accept (and ignore) null values instead of generating an error. Currently,
IGNORE_NULL is the only legal value.
decimalList
The subsequent number(s) in the sum, must have the same units and currency as the first number, and must not
be null unless IGNORE_NULL is also specified.
firstNumber
The first number in the sum, must not be null unless IGNORE_NULL is also specified.
otherNumbers
The subsequent number(s) in the sum, must have the same units and currency as the first number, and must not
be null unless IGNORE_NULL is also specified.

TAN

Computes the tangent of a scalar angle given in radians. If your input is in degrees, see the RADIANS function.

Input Arguments:

radians
Scalar angle measured in radians.

TIME_FIELD

Computes an individual date or time component field and represents it numerically.

DECIMAL	TIME_FIELD(DATEFIELD DATEFIELD, DATE DATE) EXAMPLES: TIME_FIELD(QUARTER, #2021-05-15#) = 2 TIME_FIELD(DAY_OF_YEAR, #2020-12-31#) = 366 TIME_FIELD(DAY_OF_QUARTER, #2021-09-30#) = 92 TIME_FIELD(DAY_OF_MONTH, #2021-05-31#) = 31 TIME_FIELD(DAY_OF_WEEK, #2021-01-03#) = 7 TIME_FIELD(ISO_WEEK, #2021-01-03#) = 53 TIME_FIELD(ISO_WEEK_YEAR, #2021-01-03#) = 2020
decimal	TIME_FIELD(DateField dateField, datetime dateTime) Ignores time fields of the input, equivalent to first converting the input to a date without time: TIME_FIELD(dateField, DATE(dateTime))
decimal	TIME_FIELD(TimeField timeField, datetime dateTime) Examples: TIME_FIELD(HOURS, #2022-03-28T14:23:56#) = 14 TIME_FIELD(MINUTES, #2022-03-28T14:23:56#) = 23 TIME_FIELD(SECONDS, #2022-03-28T14:23:56#) = 56

Input Arguments:

date
The date value for extracting date-related fields.
dateField (keyword type: DateField)
Allowed values: YEAR, QUARTER, MONTH, DAY_OF_YEAR, DAY_OF_QUARTER, DAY_OF_MONTH, DAY_OF_WEEK, ISO_WEEK, ISO_WEEK_YEAR
Selects which date component is to be computed and represented numerically. Result values are one(1)-based
and follow standard ranges. Important ranges for a select subset of supported fields are:
- QUARTER: 1-4
- MONTH: 1-12
- DAY_OF_YEAR: 1-366
- DAY_OF_QUARTER: 1-92
- DAY_OF_WEEK: 1=Monday, 2=Tuesday, ..., 7=Sunday
- ISO_WEEK: 1-53
  dateTime
  The date-time value for extracting date- or time-related fields.
timeField (keyword type: TimeField) Allowed values: HOURS, MINUTES, SECONDS
Selects which time component is to be computed and represented numerically. Result values for the various
fields begin at zero(0) and increase up to 23 for HOURS, or 59 for MINUTES and SECONDS.

TIME_FIELD_STRING

Computes an individual date or time component field and represents it as text. Numeric values will be padded with leading zeroes(0) as necessary to create a constant length output for a given field and, in some cases such as ISO weeks or quarters, may be prefixed with one or more alphabetic characters.

TIME_FIELD_STRING(DATEFIELD DATEFIELD, DATE DATE) EXAMPLES: TIME_FIELD_STRING(YEAR, #2021-02-04#) = "2021" TIME_FIELD_STRING(QUARTER, #2021-02-04#) = "Q1" TIME_FIELD_STRING(MONTH, #2021-02-04#) = "02" STRING • TIME_FIELD_STRING(DAY_OF_YEAR, #2021-02-04#) = "035" TIME_FIELD_STRING(DAY_OF_QUARTER, #2021-09-30#) = "92" TIME_FIELD_STRING(DAY_OF_MONTH, #2021-02-04#) = "04" TIME_FIELD_STRING(DAY_OF_WEEK, #2021-02-11#) = "4" TIME_FIELD_STRING(ISO_WEEK, #2021-01-03#) = "W53" TIME_FIELD_STRING(ISO_WEEK_YEAR, #2021-01-03#) = "2020"
string	TIME_FIELD_STRING(DateField dateField, datetime dateTime) Ignores time fields of the input, equivalent to first converting the input to a date without time: TIME_FIELD_STRING(dateField, DATE(dateTime))
string	TIME_FIELD_STRING(TimeField timeField, datetime dateTime) Examples: TIME_FIELD_STRING(HOURS, #2022-03-28T01:02:04#) = "01" TIME_FIELD_STRING(MINUTES, #2022-03-28T01:02:04#) = "02" TIME_FIELD_STRING(SECONDS, #2022-03-28T01:02:04#) = "04"

Input Arguments:

date
The date value for extracting date-related fields.
dateField (keyword type: DateField)
Allowed values: YEAR, QUARTER, MONTH, DAY_OF_YEAR, DAY_OF_QUARTER, DAY_OF_MONTH, DAY_OF_WEEK, ISO_WEEK, ISO_WEEK_YEAR
Selects which date component is to be computed and represented textually. Result values are one(1)-based and
follow standard ranges. Important ranges for a select subset of supported fields are:
- QUARTER: Q1-Q4
  - MONTH: 01-12
  - DAY_OF_YEAR: 001-366
- DAY_OF_QUARTER: 01-92
- DAY_OF_WEEK: 1=Monday, 2=Tuesday, ..., 7=Sunday
- ISO_WEEK: W01-W53
  dateTime
  The date-time value for extracting date- or time-related fields.
timeField (keyword type: TimeField) Allowed values: HOURS, MINUTES, SECONDS
Selects which time component is to be computed and represented textually. Result values for the various
fields begin at zero(00) and increase up to 23 for HOURS, or 59 for MINUTES and SECONDS.

TRIM

Accepts a string and removes the leading and trailing white spaces. If the input string is null, the output is also null. Characters are considered whitespace according to the Unicode standard, excluding non-breaking spaces, and also includes line endings and certain rarely-used record separator characters.

Input Arguments:

toTrim
The string to be trimmed.

TRYINORDER

This function evaluates a set of expressions in their listed order until it finds one that evaluates successfully and yields a non-null result. All input values must be the same type of data. If all inputs error or return null, this function will return null but not cause an error.

BOOLEAN	TRYINORDER(@NULLABLE BOOLEAN FIRSTVALUE, [@NULLABLE BOOLEAN MOREVALUES]...)
currency	TRYINORDER(@Nullable currency firstValue, [@Nullable currency moreValues]...)
date	TRYINORDER(@Nullable date firstValue, [@Nullable date moreValues]...)
datetime	TRYINORDER(@Nullable datetime firstValue, [@Nullable datetime moreValues]...)
decimal	TRYINORDER(@Nullable decimal firstValue, [@Nullable decimal moreValues]...)
string	TRYINORDER(@Nullable string firstValue, [@Nullable string moreValues]...)
unit	TRYINORDER(@Nullable unit firstValue, [@Nullable unit moreValues]...)

Input Arguments:

firstValue
The first expression to attempt. At least one expression is required.

moreValues

Subsequent expression(s) to evaluate in order.

UNIT

Returns a UNIT type from the input. The UNIT type is a "first class" type in the formula engine and

can be the result of an element formula or other calculation.

UNIT(DECIMAL DECIMALVALUE) UNIT	EXTRACTS THE UNIT FROM THE GIVEN EXPRESSION. THE EXPRESSION MUST RESULT IN A VALID, NON-NULL VALUE. IF THE EXPRESSION HAS NO ASSOCIATED UNIT, RETURNS A SPECIAL VALUE REPRESENTING THE ABSENCE OF A UNIT.
unit	UNIT(@Nullable string unitCode) Returns a UNIT type with the given unit code. The leading and trailing whitespace in the unit code will be trimmed. An empty or whitespace only unit code will return a special value representing the absence of a unit.

Input Arguments:

decimalValue
The decimal value from which to extract the associted unit.

unitCode

The string representation of the unit to create.

UPPER

Applies case-folding rules to convert the input string to uppercase.

STRING	UPPER(@NULLABLE STRING VALUE) APPLIES DEFAULT CASING RULES WITHOUT SPECIAL LANGUAGE SUPPORT.
string	UPPER(@Nullable string value, @Nullable string languageTag) Case-folding rules and therefore the resulting output may vary by language, such as German, Turkish, and others. Use this signature to apply rules for a specific language.

Input Arguments:

languageTag
The language tag representing the locale to use to convert the string to upper case, in IETF BCP

47 language

tag format ("en-US", "en", "jp-JP"). If the language tag is null, invalid, the special value "und", or the

special value "zxx", then default casing rules without special language support are used instead.

value

The string to be converted. If null, the result of the function will be null.

WEEKDAY

This function returns the day of week for the given input DATE or DATETIME. The returned day of week is represented according to the ISO 8601 standard, with values from 1 to 7 where 1 represents Monday, and 7 represents Sunday. See also: TIME_FIELD

DECIMAL	WEEKDAY(DATE INPUTDATE) RETURNS THE DAY OF WEEK FOR THE GIVEN DATE.
decimal	WEEKDAY(datetime inputDateTime) Returns the day of week for the given DATETIME.

Input Arguments:

inputDate
The DATE value to use to resolve the day of week.

inputDateTime

The DATETIME value to use to resolve the day of week.

Aggregation functions

VAGGREGATE

Returns the vertical aggregation function specified by the aggregation operator (enum of SUM, MIN, MAX, AVERAGE). The scope indicate which rows should be considered. It is an enum of :

ALL: all rows of the quote
CHILDREN only direct children of the current row
ROOT all rows at the root of the spreadsheet meaning all rows that aren't a sub row of another. If a condition is specified, the valueIf will be considered in the aggregation if the condition is respected. Otherwise, the valueElse (or an neutral element if valueElse isn't specified) is aggregated in the computation. The row templates further reduces the scope by limiting the aggregation to certain row templates.
decimal VAGGREGATE(aggregationOperator aggOpe, decimal value, scope scope)
Returns the vertical aggOpe function on value, with a scope.
decimal VAGGREGATE(aggregationOperator aggOpe, boolean condition, decimal valueIf, scope scope)
Returns the vertical aggOpe function on valueIf if the specified condition is respected, with a scope.
decimal VAGGREGATE(aggregationOperator aggOpe, boolean condition, decimal valueIf, decimal valueElse, scope scope)
Returns the vertical aggOpe function on valueIf if the specified condition is respected, or on valueElse, with a scope.
decimal VAGGREGATE(aggregationOperator aggOpe, decimal value, scope scope,
list<string> rowTemplates)

Returns the vertical aggOpe function on value, with a scope and a row templates list.
decimal VAGGREGATE(aggregationOperator aggOpe, boolean condition, decimal valueIf, scope scope, list<string> rowTemplates)
Returns the vertical aggOpe function on valueIf if the specified condition is respected, with a scope and a row templates list.
decimal VAGGREGATE(aggregationOperator aggOpe, boolean condition, decimal valueIf, decimal valueElse, scope scope, list<string> rowTemplates)
Returns the vertical aggOpe function on valueIf if the specified condition is respected, or on valueElse, with a scope and a row templates list.

Returns the vertical aggregation function specified by the aggregation operator CONCATENATE. The scope indicate which rows should be considered. It is an enum of :

ALL: all rows of the quote
CHILDREN only direct children of the current row
ROOT all rows at the root of the spreadsheet meaning all rows that aren't a sub row of another. If a condition is specified, the valueIf will be considered in the aggregation if the condition is respected. Otherwise, the valueElse (or an neutral element if valueElse isn't specified) is aggregated in the computation. The row templates further reduces the scope by limiting the aggregation to certain row templates.
string VAGGREGATE(CONCATENATE, string value, scope scope)
Returns the vertical concatenate function on value, with a scope.
string VAGGREGATE(CONCATENATE, boolean condition, string valueIf, scope scope)
Returns the vertical concatenate function on valueIf if the specified condition is respected, with a scope.
string VAGGREGATE(CONCATENATE, boolean condition, string valueIf, string valueElse, scope scope)
Returns the vertical concatenate function on valueIf if the specified condition is respected, or on valueElse, with a scope.
string VAGGREGATE(CONCATENATE, string value, scope scope, list<string> rowTemplates)
Returns the vertical concatenate function on value, with a scope and a row templates list.
string VAGGREGATE(CONCATENATE, boolean condition, string valueIf, scope scope, list<string> rowTemplates)
Returns the vertical concatenate function on valueIf if the specified condition is respected, with a scope and a row templates list.
string VAGGREGATE(CONCATENATE, boolean condition, string valueIf, string valueElse, scope scope, list<string> rowTemplates)
Returns the vertical concatenate function on valueIf if the specified condition is respected, or on valueElse, with a scope and a row templates list.

VAVERAGE

Returns the vertical average for the value. This formula will be set on fields.

decimal VAVERAGE(decimal value)
Returns the vertical average for the value. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

VAVERAGE_CHILDREN

Returns the vertical average restricted to the children for the value. This formula will be set on folder or pack rows.

decimal VAVERAGE_CHILDREN(decimal value)
Returns the vertical average for the value. This formula will be set on folder or pack rows. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

VAVERAGE_CHILDREN_IF

Returns the vertical average with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average. This formula will be set on folder or pack rows.

decimal VAVERAGE_CHILDREN_IF(boolean condition, decimal valueIf)
Returns the vertical average with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical average with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average. Otherwise, the valueElse is aggregated in the average computation. This formula will be set on folder or pack rows.

decimal VAVERAGE_CHILDREN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical average (arithmetic mean) with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average.

Otherwise, the valueElse is aggregated in the average computation. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

VAVERAGE_IF

decimal VAVERAGE_IF(boolean condition, decimal valueIf)
Returns the vertical average with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

decimal VAVERAGE_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical average (arithmetic mean) with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the average.

Otherwise, the valueElse is aggregated in the average computation. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected field currency).

VCONCATENATE

Returns a concatenation of all the values (value + otherValues), line by line. This formula will be set on fields.

string VCONCATENATE(string value, list<string> otherValues)
Returns a concatenation of all the values (value + otherValues), line by line. Values provided can:
be NoValue, they aren't considered during computation.

VCONCATENATE_UNIQUE

Returns the concatenation of all the unique occurrence of expression, separated by the specified separator (it has to be a constant) and an eventual restriction on line templates. The concatenation is sorted respecting the alphabetical order. This formula will be set on fields.

string VCONCATENATE_UNIQUE(string expression, string separator, list<string> lineTemplates)
Returns the concatenation of all the unique occurrence of expression. Values provided can:
be NoValue, they aren't considered during computation.

Returns the concatenation of all the unique occurrence of expression, separated by the specified separator (it has to be a constant) and an eventual restriction on line templates. The concatenation is sorted respecting the numerical order. This formula will be set on fields.

string VCONCATENATE_UNIQUE(decimal expression, string separator, list<string> lineTemplates)
Returns the concatenation of all the unique occurrence of expression. Values provided can:
be NoValue, they aren't considered during computation.

VCONCATENATE_UNIQUE_CHILDREN

Returns the concatenation of all the unique occurrence of expression, with a specified separator (it has to be a constant) and an eventual restriction on line templates. The concatenation is sorted respecting the alphabetical order. This formula will be set on cells.

string VCONCATENATE_UNIQUE_CHILDREN(string expression, string separator, list<string> lineTemplates)
Returns the concatenation of all the unique occurrence of expression. Values provided can:
be NoValue, they aren't considered during computation.

Returns the concatenation of all the unique occurrence of expression, with a specified separator (it has to be a constant) and an eventual restriction on line templates. The concatenation is sorted respecting the numerical order. This formula will be set on cells.

string VCONCATENATE_UNIQUE_CHILDREN(decimal expression, string separator, list<string> lineTemplates)
Returns the concatenation of all the unique occurrence of expression. Values provided can:
be NoValue, they aren't considered during computation.

VCOUNT

Returns the number of set values in the column. This formula will be set on fields.

decimal VCOUNT(decimal value)
Returns the number of set values in the column. NoValue aren't considered during computation.

Returns the number of set values in the column. This formula will be set on fields.

decimal VCOUNT(string value)

VCOUNT_CHILDREN

Returns the number of set values in the column. This formula will be set on folder or pack rows.

decimal VCOUNT(decimal value)
Returns the number of set values in the column. NoValue aren't considered during computation.

Returns the number of set values in the column. This formula will be set on folder or pack rows.

decimal VCOUNT(string value)
Returns the number of set values in the column. NoValue aren't considered during computation.

VCOUNT_IF

Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. This formula will be set on fields.

decimal VCOUNT_IF(boolean condition, decimal valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. This formula will be set on fields.

decimal VCOUNT_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation.

NoValue aren't considered during computation.

decimal VCOUNT_IF(boolean condition, string valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

decimal VCOUNT_IF(boolean condition, string valueIf, string valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. NoValue aren't considered during computation.

decimal VCOUNT_IF(boolean condition, boolean valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

Returns the number of set values in the column. If the condition is respected, the valueIf (which can

be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. This formula will be set on fields.

decimal VCOUNT_IF(boolean condition, boolean valueIf, boolean valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. NoValue aren't considered during computation.

VCOUNT_CHILDREN_IF

decimal VCOUNT_CHILDREN_IF(boolean condition, decimal valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

decimal VCOUNT_CHILDREN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. NoValue aren't considered during computation.

decimal VCOUNT_CHILDREN_IF(boolean condition, string valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

decimal VCOUNT_CHILDREN_IF(boolean condition, string valueIf, string valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. NoValue aren't considered during computation.

decimal VCOUNT_CHILDREN_IF(boolean condition, boolean valueIf)
Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. NoValue aren't considered during computation.

Returns the number of set values in the column. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is

aggregated in the count computation. This formula will be set on folder or pack rows.

decimal VCOUNT_CHILDREN_IF(boolean condition, boolean valueIf, boolean valueElse)
Returns the number of set values in the column for which the condition is fulfilled. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the count. Otherwise, the valueElse is aggregated in the count computation. NoValue aren't considered during computation.

VMAX

Returns the vertical max for the value. This formula will be set on fields.

decimal VMAX(decimal value)
Returns the vertical max for the value. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMAX_CHILDREN

Returns the vertical max restricted to the children for value. This formula will be set on folder or pack rows.

decimal VMAX_CHILDREN(decimal value)
Returns the vertical max for the value. This formula will be set on folder or pack rows. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMAX_CHILDREN_IF

Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. This formula will be set on folder or pack rows.

decimal VMAX_CHILDREN_IF(boolean condition, decimal valueIf)
Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Otherwise, the valueElse is aggregated in the max computation. This formula will be set on folder or pack rows.

decimal VMAX_CHILDREN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Otherwise, the valueElse is aggregated in the max computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMAX_IF

Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. This formula will be set on fields.

decimal VMAX_IF(boolean condition, decimal valueIf)
Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Otherwise, the valueElse is aggregated in the max computation.

decimal VMAX_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical max with a condition. If the condition is respected, the valueIf will be considered in the max. Otherwise, the valueElse is aggregated in the max computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMIN

Returns the vertical min for the value. This formula will be set on fields.

decimal VMIN(decimal value)
Returns the vertical min for the value. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMIN_CHILDREN

Returns the vertical min restricted to the children for the value. This formula will be set on folder or pack rows.

decimal VMIN_CHILDREN(decimal value)
Returns the vertical min for the value. This formula will be set on folder or pack rows. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMIN_CHILDREN_IF

Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. This formula will be set on folder or pack rows.

decimal VMIN_CHILDREN_IF(boolean condition, decimal valueIf)
Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical min with a condition. If the condition is respected, the valueIf (which can be a column or a linear expression) will be considered in the min. Otherwise, the valueElse is aggregated in the min computation. This formula will be set on folder or pack rows.

decimal VMIN_CHILDREN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. Otherwise, the valueElse is aggregated in the min computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VMIN_IF

Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. This formula will be set on fields.

decimal VMIN_IF(boolean condition, decimal valueIf)
Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. Otherwise, the valueElse is aggregated in the min computation.

decimal VMIN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical min with a condition. If the condition is respected, the valueIf will be considered in the min. Otherwise, the valueElse is aggregated in the min computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM

Returns the vertical sum for the value. This formula will be set on fields.

decimal VSUM(decimal value)
Returns the vertical sum for the value. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_ROOT

Returns the vertical sum of all lines at the hierarchical root level of the grid. This formula will be set on fields.

decimal VSUM_ROOT(decimal value)
Returns the vertical sum of all lines at the hierarchical root level of the grid. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_CHILDREN

Returns the vertical sum restricted to the children for the value. This formula will be set on folder or pack rows.

decimal VSUM_CHILDREN(decimal value)
Returns the vertical sum for the value. This formula will be set on folder or pack rows. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_CHILDREN_IF

Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. This formula will be set on folder or pack rows.

decimal VSUM_CHILDREN_IF(boolean condition, decimal valueIf)
Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation. This formula will be set on folder or pack rows.

decimal VSUM_CHILDREN_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_IF

Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. This formula will be set on fields.

decimal VSUM_IF(boolean condition, decimal valueIf)
Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation.

decimal VSUM_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical sum with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_ROOT_IF

Returns the vertical sum of all lines at the hierarchical root level of the grid with a condition. If the condition is respected, the valueIf will be considered in the sum. This formula will be set on fields.

decimal VSUM_ROOT_IF(boolean condition, decimal valueIf)
Returns the vertical sum of all lines at the hierarchical root level of the grid with a condition. If the condition is respected, the valueIf will be considered in the sum. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

Returns the vertical sum of all lines at the hierarchical root level of the grid with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation.

decimal VSUM_ROOT_IF(boolean condition, decimal valueIf, decimal valueElse)
Returns the vertical sum of all lines at the hierarchical root level of the grid with a condition. If the condition is respected, the valueIf will be considered in the sum. Otherwise, the valueElse is aggregated in the sum computation. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_PROD

Returns the vertical product sum for the value. This formula will be set on fields.

decimal VSUM_PROD(decimal value, list<decimal> otherValues)
Returns the vertical product sum for tvalue. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_PROD_CHILDREN

Returns the vertical product sum restricted to the children for the value. This formula will be set on folder or pack rows.

decimal VSUM_PROD_CHILDREN(decimal value, list<decimal> otherValues)
Returns the vertical product sum for the value. This formula will be set on folder or pack rows. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_PROD_CHILDREN_IF

Returns the vertical product sum with a condition. If the condition is respected, the resulting product value will be considered in the sum. This formula will be set on folder or pack rows.

decimal VSUM_PROD_CHILDREN_IF(boolean condition, decimal value, list<decimal> otherValues)
Returns the vertical product sum with a condition. If the condition is respected, the resulting product value will be considered in the sum. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSUM_PROD_IF

Returns the vertical product sum with a condition. If the condition is respected, the resulting product value will be considered in the sum. This formula will be set on fields.

decimal VSUM_PROD_IF(boolean condition, decimal value, list<decimal> otherValues)
Returns the vertical product sum with a condition. If the condition is respected, the resulting product value will be considered in the sum. Values provided can:
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected field currency).

VSEARCH

VSEARCH is a formula used to assign a value on a given row and given column.

VSEARCH returns the result of a formula. The formula can target cells of a matching row (and the parent of matching row) and fields.

The matching row is found by searching against a list of line templates. The search is based on a “current” column, and an “other” column. A match is found when the value of current line on “current” column is equal to the value of another line on the “other” column. The “current” and “other” column can be a same column, or different columns.

If more than one matching row is found, an error is returned.

decimal VSEARCH(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the string criteria.
decimal VSEARCH(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.
string VSEARCH(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.
string VSEARCH(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.

VSEARCH_MULTIPLE

VSEARCH_MULTIPLE is a formula used to assign a value on a given row and given column.

VSEARCH_MULTIPLE returns the result of a formula. The formula can target cells of a matching row (and the parent of matching row) and fields.

If more than one matching row is found, an error is returned if the different rows have different values on the “other” column.

decimal VSEARCH_MULTIPLE(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the string criteria.
decimal VSEARCH_MULTIPLE(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.
string VSEARCH_MULTIPLE(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.
string VSEARCH_MULTIPLE(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the value on another row that match the decimal criteria.

VSUM_BY

VSUM_BY compute the sum of all rows that have the same criteria value as the current one. The formula is executed on the other matching row. The formula can target matching row cells, matching row parent cells, and fields.

decimal VSUM_BY(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the sum of all formula of other row with same string criteria
decimal VSUM_BY(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the sum of all formula of other row with same decimal criteria
string VSUM_BY(string colCurrent, string colOther, string formula, List<string> lineTemplates)
Returns the concatenation of all formula of other row with same string criteria
string VSUM_BY(decimal colCurrent, decimal colOther, string formula, List<string> lineTemplates)
Returns the concatenation of all formula of other row with same decimal criteria

PSUM

Returns the vertical sum for all periods of a row. This formula will be set on the main row.

decimal PSUM(decimal value)
Returns the vertical sum for the value. Values provided can:
be a formula executed on each period
be NoValue, they aren't considered during computation;
have different currencies (a conversion is done into the expected cell currency).

PMAX

Returns the maximum value for all periods of a row. This formula will be set on the main row.

decimal PMAX(decimal value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).
LocalDate PMAX(LocalDate value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).
LocalDateTime PMAX(LocalDateTime value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).

PMIN

Returns the minimum value for all periods of a row. This formula will be set on the main row.

decimal PMIN(decimal value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).
LocalDate PMIN(LocalDate value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).
LocalDateTime PMIN(LocalDateTime value)
Returns the maximum value for all periods of a row. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done into the expected cell currency).

PCOUNT

Returns the number of set values in the column for all periods of a row. This formula will be set on the main row.

decimal PCOUNT(decimal value)
Returns the number of set values in the column. NoValue aren't considered during computation.
decimal PCOUNT(String value)
Returns the number of set values in the column. NoValue aren't considered during computation.
decimal PCOUNT(Boolean value)
Returns the number of set values in the column. NoValue aren't considered during computation.
decimal PCOUNT(LocalDate value)
Returns the number of set values in the column. NoValue aren't considered during computation.
decimal PCOUNT(LocalDateTime value)
Returns the number of set values in the column. NoValue aren't considered during computation.

PAVERAGE

Returns the average for the value for all periods of a row. This formula will be set on the main row.

decimal PAVERAGE(decimal value)
Returns the vertical average for the value. Values provided can:
be NoValue, they aren't considered during average computation;
have different currencies (a conversion is done into the expected cell currency).

PSEARCH

Compute a formula for the period that contains the provided date. If no period match, null is returned by the function.

decimal PSEARCH(LocalDate searchDate, decimal value)
Compute a formula for the period that contains the provided local date. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done if needed)
decimal PSEARCH(LocalDateTime searchDate, decimal value)
Compute a formula for the period that contains the provided datetime. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
have different currencies (a conversion is done if needed)
String PSEARCH(LocalDate searchDate, String value)
Compute a formula for the period that contains the provided local date. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
String PSEARCH(LocalDateTime searchDate, String value)
Compute a formula for the period that contains the provided datetime. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
LocalDate PSEARCH(LocalDate searchDate, LocalDate value)
Compute a formula for the period that contains the provided local date. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
LocalDate PSEARCH(LocalDateTime searchDate, LocalDate value)
Compute a formula for the period that contains the provided datetime. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
LocalDateTime PSEARCH(LocalDate searchDate, LocalDateTime value)
Compute a formula for the period that contains the provided local date. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
LocalDateTime PSEARCH(LocalDateTime searchDate, LocalDateTime value)
Compute a formula for the period that contains the provided datetime. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
Boolean PSEARCH(LocalDate searchDate, Boolean value)
Compute a formula for the period that contains the provided local date. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period
Boolean PSEARCH(LocalDateTime searchDate, Boolean value)
Compute a formula for the period that contains the provided datetime. If no period match, null is returned by the function. Values provided can:
be a formula executed on each period

Performance Quoting functions

IS_NO_VALUE

Returns true if the given value has no value.

boolean IS_NO_VALUE(boolean value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(string value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(decimal value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(currency value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(unit value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(LocalDate value)
Returns true if the given value has no value.
boolean IS_NO_VALUE(LocalDateTime value)
Returns true if the given value has no value.

RAW_VALUE

Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.

boolean RAW_VALUE(boolean value)
Returns the variable value or NO_VALUE if not set.
string RAW_VALUE(string value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.
decimal RAW_VALUE(decimal value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.
currency RAW_VALUE(currency value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.
unit RAW_VALUE(unit value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.
LocalDate RAW_VALUE(LocalDate value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.
LocalDateTime RAW_VALUE(LocalDateTime value)
Returns the variable value or NULL if not set. NULL value are considered as NO_VALUE if it is the final result of the formula.

IF_ELSE_NULL

Returns the given value if the given condition is fulfilled, null otherwise.

string IF_ELSE_NULL(boolean condition, string value)
Returns the given value if the given condition is fulfilled, null otherwise.
decimal IF_ELSE_NULL(boolean condition, decimal value)
Returns the given value if the given condition is fulfilled, null otherwise.
decimal IF_ELSE_NULL(boolean condition, decimal value)
Returns the given value if the given condition is fulfilled, null otherwise.
LocalDate IF_ELSE_NULL(boolean condition, LocalDate value)
Returns the given value if the given condition is fulfilled, null otherwise.
LocalDateTime IF_ELSE_NULL(boolean condition, LocalDateTime value)
Returns the given value if the given condition is fulfilled, null otherwise.
boolean IF_ELSE_NULL(boolean condition, boolean value)
Returns the given value if the given condition is fulfilled, null otherwise.

IF_ELSE_KEEP_CURRENT

Returns the given value if the given condition is fulfilled, otherwise the current value is kept. This function can only be used as single top level of the formula. Meaning, you can't use the formula like this : otherFormula(IF_ELSE_KEEP_CURRENT(..)) or like this IF_ELSE_KEEP_CURRENT(..) + otherFormula. Meanwhile, the condition and the value inside the IF_ELSE_KEEP_CURRENT can be anything (function, operator, constant, ...)(

boolean forceComputation

Controls the force computation of the formula in case of an error with the current value.

In other words if the condition fails, and the formula keeps the current value, but the current value is in error, then the formula will run as if the condition was equal to "true".

If forceComputation = "false", condition = "false" and current value = <error> - the result of the formula is an error.

If forceComputation = "true", condition = "false" and current value = <error> - the formula computation will run as if condition = "true".

string IF_ELSE_KEEP_CURRENT(boolean condition, string value, boolean forcedComputation)
Returns the given value if the given condition is fulfilled, unchanged the current result otherwise.
decimal IF_ELSE_KEEP_CURRENT(boolean condition, decimal value, boolean forceComputation)
Returns the given value if the given condition is fulfilled, unchanged the current result otherwise.
LocalDateTime IF_ELSE_KEEP_CURRENT(boolean condition, LocalDateTime value, boolean forceComputation)
Returns the given value if the given condition is fulfilled, unchanged the current result otherwise.
LocalDate IF_ELSE_KEEP_CURRENT(boolean condition, LocalDate value, boolean forceComputation)
Returns the given value if the given condition is fulfilled, unchanged the current result otherwise.
boolean IF_ELSE_KEEP_CURRENT(boolean condition, boolean value, boolean forceComputation)
Returns the given value if the given condition is fulfilled, unchanged the current result otherwise.

ERROR_VALUE

Raise an exception with an error message

string ERROR_VALUE(string errorMessage)
Raise an exception with an error message.

CURRENCYCODE

Get the currency code of a currency variable.

string CURRENCYCODE(string currencyValue)
Returns the code for the given currency value.

Get the currency code of a monetary variable.

string CURRENCYCODE(string monetaryValue)
Returns the currency code for the given monetary value.

COMMAND_DATE

Get the date of the command as a LocalDate.

date COMMAND_DATE()
Returns the date request execution result as a LocalDate.

COMMAND_DATETIME

Get the date of the command as a DateTime.

datetime COMMAND_DATETIME()
Returns the date request execution result as a DateTime.

URL_VALUE

Create an url value from the provided url.

string URL_VALUE(string url)
- Returns url value for a given url

Create an url value from the provided url and label.

string URL_VALUE(string url, string label)
- Returns url value for a given url and label

Create an url value from the provided url, label and tooltip.

string URL_VALUE(string url, string label, string tooltip)
- Returns url value for a given url, label and tooltip

URL_TARGET

Extract the url target from the provided url value.

string URL_TARGET(string urlvalue)
- Returns the target for a given url value

URL_LABEL

Extract the url label from the provided url value.

string URL_LABEL(string urlvalue)
- Returns the label for a given url value

URL_TOOLTIP

Extract the url tooltip from the provided url value.

string URL_TOOLTIP(string urlvalue)
- Returns the tooltip for a given url value

BUSINESS_VALUE

Create a business value from the provided strings. The name not be null nor empty. Valid values for type are SI, CP, SBL, BVAL, DIMENSION. The description will be the same as the provided name

string BUSINESS_VALUE(string name, string type)
Returns the corresponding businessValue.

Create a business value from the provided strings. The name not be null nor empty. Valid values for type are SI, CP, SBL, BVAL, DIMENSION. The description will be the same as the provided name The description will be the same as the provided name if description value is empty but it cannot be null.

string BUSINESS_VALUE(string name, string type, string description)
Returns the corresponding business value.

string BUSINESS_VALUE(string name, string type, string description, string workspace)
Returns the corresponding business value.

DESCR

Extract the description from the provided business value.

string DESCR(string businessValue)
Returns the description for the provided business value.

NAME

Extract the name from the provided business value.

string NAME(string businessValue)
Returns the name for the provided business value.

NAMESPACE

Extract the namespace from the provided business value.

string NAMESPACE(string businessValue)
Returns the namespace for the provided business value.

Extract the pk from the provided business value.

string PK(string businessValue)
Returns the pk for the provided business value.

TYPE

Extract the type from the provided business value.

string TYPE(string businessValue)
Returns the type for the provided business value.

ASPECT_NAME

Extract the aspect name from the provided business value.

string ASPECT_NAME(string businessValue)
Returns the aspect name for the provided business value.

ASPECT_VALUE

Extract the aspect value from the provided business value.

string ASPECT_VALUE(string businessValue, string aspectName)
Returns the aspect value for the provided business value and the required aspect name.

REPLACED

Extract the original value of replaced product.

string REPLACED(_SYS_ROW_ITEM)
Returns the original row item which has been replaced.
decimal REPLACED(_SYS_ROW_QUANTITY)
Returns the original quantity which has been replaced.

SCALE_BASE_PRICE

Extract the base price of the scale grid value.

decimal SCALE_BASE_PRICE(string scaleGridValue)
Returns the base price of the scale grid value or null.

SCALE_UOM

Extract the unit of measure of the scale grid value structure.

unit SCALE_UOM(string scaleGridValue)
Returns the unit of measure of the scale grid value structure or null.

MAX_SCALED_PRICE

Retrieve the maximum projected price within a given scale grid.

decimal MAX_SCALED_PRICE(string scaleGridValue)
Returns the maximum projected price of the scale grid value or null.

MIN_SCALED_PRICE

Retrieve the minimum projected price within a given scale grid.

decimal MIN_SCALED_PRICE(string scaleGridValue)
Returns the minimum projected price of the scale grid value or null.

NUMBER_SCALED_PRICE

Retrieve the number of scale breaks in a scale grid.

integer NUMBER_SCALED_PRICE(string scaleGridColumn)
Returns the number of scale breaks in a scale grid.

PERIOD_STARTDATE

Extract the start date from the provided period value.

string PERIOD_STARTDATE(string periodValue)
Returns the start date for the provided period value.

PERIOD_ENDDATE

Extract the end date from the provided period value.

string PERIOD_ENDDATE(string periodValue)
Returns the end date for the provided period value.

PARENT

Get the parent value. A property (NAME, DESCR, ... ) can be specified for business value.

string PARENT(string value)
Returns the parent value.
decimal PARENT(decimal value)
Returns the parent value.
string PARENT(string businessValue, string property)
Returns the parent of the given businessValue specified by the property.

PARENT_IF

Get the parent value with a condition. A property (NAME, DESCR, ... ) can be specified for business value.

string PARENT_IF(boolean condition, string value)
Returns the parent value following the condition.
decimal PARENT_IF(boolean condition, decimal value)
Returns the parent value following the condition.
string PARENT_IF(boolean condition, string businessValue, string property)
Returns the parent of the given businessValue specified by the property following the condition.

EXECUTEXPATH

Execute a XPath function. Execute the request into the xml file .

string EXECUTEXPATH(string xmlContent, string request)
Returns the request execution result.

EXECUTEJSONPATH

Execute a JsonPath function on a String value. Otherwise, if no results are found, an empty string is

return.

string EXECUTEJSONPATH(string jsonContent, string request)
Returns the string request execution result.

EXECUTEJSONPATH_DECIMAL

Execute a JsonPath function on a Decimal value. Otherwise, if no results are found, an empty string is return.

decimal EXECUTEJSONPATH_DECIMAL(string jsonContent, string request)
Returns the decimal request execution result.

EXECUTEJSONPATH_BOOLEAN