-
Absolute Value: Returns the absolute value of a specified number. If a number is less than zero, the positive value of the number is returned. If the specified number is equal to or greater than zero, the specified number is returned.
ABS(<Number>)
-
Add Month: Returns a date offset a specified number of months from the starting date. The date will always fall in the specified month offset. If the starting date has a day value beyond what is in the offset month, the last day of the offset month will be used. For example, EDate (31-Jan-2017, 1) returns (28-Feb-2017). For Months, enter the number of months before or after the start date. A positive value for months yields a future date. A negative value yields a past date.
ADD_MONTH(<Start Date>, <Months>, <Length>)
Example: ADD_MONTH(DATE(2017, 2, 15) 3)
-
Average Prior: Averages a numeric amount over the prior X periods.
AVERAGE_PRIOR(<Value>, <Number of Periods>, <To Currency*>
Example: AVERAGE_PRIOR( {Balance (Reporting)}, '2',
'EUR'
-
Date: Returns a date value based on specified integer values for the year,
month, and day.
DATE(<Year>, <Month>, <Day>)
-
Date Difference: Returns the difference in days, hours, minutes, or
seconds between two dates. For DATE 1 and DATE 2, the values TODAY
and NOW can be used, which denote the current date (with no time
component) and date-time, respectively.
DATE_DIFF(<Date1>, <Date2>, <Type>)
Example: DATE_DIFF('TODAY', {Preparer End Date}, 'DAYS') or DATE_DIFF({Preparer End Date}, 'NOW', 'HOURS')
-
Day: Returns the day value of a date as an integer number
DAY(<DATE>)
-
Extract Text: Returns the substring within the value, from the positions specified.
SUBSTRING(<Value>, <Location>, <Length>)
Example: SUBSTRING( {Name} , 5, 10)
-
If Then Else: Allows the user to insert a conditional calculation into the scripted calculation. IF_THEN_ELSE calculations can also be nested to support ELSE IF type calculations.
IF_THEN_ELSE(<Condition>, <Value1>, <Value2>)
Example:
IF_THEN_ELSE( {Risk Rating} = 'Low', 'Good',
IF_THEN_ELSE( {Risk Rating} = 'Medium', 'Better',
IF_THEN_ELSE({Risk Rating} = 'High', 'Best','Bad')))
-
Length: Takes a text value as a parameter and returns an integer which is the number of characters in the text. If the value is empty/null, the calculation will return 0.
Length ({<attribute>})
Example: LENGTH('Value') would return 5, and LENGTH({Name}) would return the number of characters in the name of the object.
Use the calculation with SUBSTRING to extract the last four characters of a text value.
SUBSTRING( {MyString}, LENGTH ({MyString}) - 4
-
Lowercase: Returns the value in lower case.
LOWERCASE(<Value>)
Example: LOWERCASE( {Description} )
-
Maximum: Returns the maximum value from a list of attributes. There can be any number of parameters.
MAX(<Value1>, <Value2>, ..., <ValueN>)
Example: MAX( TRANSLATE( {Source System Balance (Entered)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Functional)}, 'USD', 'Accounting'), TRANSLATE( {Source System Balance (Reporting)}, 'USD', 'Accounting') )
-
Maximum Prior: Returns the maximum value over the prior X periods.
MAX_PRIOR (<Value>, <Number of Periods>)
Example: MAX_PRIOR( {Balance (Functional)}, '6', 'CAD', 'REC')
-
Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.
MIN(<Value1>, <Value2>,<ValueN>)
Example: MIN( TRANSLATE( { Balance (Entered)}, 'CAD', 'REC'), TRANSLATE( {Balance (Functional)}, 'CAD', 'REC'), TRANSLATE( {Balance (Reporting)}, 'CAD', 'REC') )
-
Minimum Prior: Returns the minimum value over the prior X periods.
MIN_PRIOR (<Value>, (<Value>, <Number of Periods>)
Example: MIN_PRIOR( {Source System Balance (Functional)}, '6', 'EUR', 'Simplified')
-
Month: Returns the month value of a date as an integer number (1-12).
MONTH (<DATE>)
-
Power: Raises one number to the exponential power of another.
POWER(x,y) where x=BASE NUMBER,and y=EXPONENT and x and y can be attributes or calculations, as long as they are numeric.
Example: POWER(3,4)=81
Note:
Fractional values will reduce the number to its root. For example,
POWER(27, 1/3) = 3 the cube root.
Negative values will perform an inverse of the exponential calculation. For example, POWER(2, -2) = 1 / (2^2) = 1 / 4 = .25.
-
Prior: Returns the value of the specified prior period.
PRIOR(<Value>, <Number of Periods Prior>)
Example: PRIOR( {Source System Balance (Entered)}, '1', 'EUR'')
-
Round: Returns the value rounded to the decimal places specified.
ROUND(<Value>, <Decimal Places>)
Example: ROUND( ({Scripted Translate} /7), 4)
-
Sum Prior: Returns the sum of a value over the prior X periods.
SUM_PRIOR(<Value>, <Number of Periods>)
Example: SUM_PRIOR( {Balance (Reporting)}, '3', 'EUR')
-
Text Location: Returns the location of the substring within the attribute value, starting at 1 as the first position.
INSTRING(<Value>, <Value To Search>)
Example: INSTRING( UPPERCASE( {Name} ), 'TAX' )
-
Translate: Translates a currency attribute to a numeric attribute using a specified rate type.
TRANSLATE(<Value>, <To Currency>, <Rate Type>)
Example: TRANSLATE( {Balance (Entered)}, 'EUR', 'Acct')
-
Uppercase: Returns the value in upper case.
UPPERCASE(<Value>)
Example: UPPERCASE( {Name} )
-
Year: Returns the year value of a date as an integer number.
YEAR (<DATE>)