-
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)
-
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') )
-
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') )
-
Month: Returns the month value of a date as an integer number (1-12).
MONTH (<DATE>)
-
Power Of: 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
.
-
Round: Returns the value rounded to the decimal places specified.
ROUND(<Value>, <Decimal Places>)
Example: ROUND( ({Scripted Translate} /7), 4)
-
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' )
-
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>)