About Calculated Attributes

Calculated attributes are read-only.

Administrators can add calculated attributes to the attributes sections in the Actions dialog boxes, and workflow users can view them in the actions dialog boxes and in transactions. Administrators can restrict access to certain roles by setting access to Do Not Display. For example, for calculated attribute XYZ, an administrator could add Viewer: Do Not Display access to it, so that XYZ would not be displayed to viewers.

Any user role can add calculated attributes as columns in views and lists. They can also be added as filterable attributes in the Filter Panel.

The following table lists the calculation types that each attribute type can use when the Calculation option is chosen:

Table 4-1 Calculation Types that Each Attribute Type Can Use When the Calculation Option is Chosen

Attribute Type	Assign Value to List	Conditional	Scripted	Assign List to Value
Date			X
Date/Time			X
Integer	X	X	X
List		X		X
Multi-Line Text	X	X	X
Number	X	X	X
Text	X	X	X
True/False		X
User
Yes/No		X

Examples of Scripted Functions

For all numeric functions, any null values in the input are ignored while computing the result.

• 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.

  EDate(<Start Date>, <Months>, <Length>)

  Example: EDate(DATE(2017, 2, 15) 3)

• Average Prior Function: Averages a numeric amount over the prior X periods.

  AVERAGE_PRIOR(<Value>, <Number of Periods>, <To Currency*>, <Rate Type*>, <Rate Period*>

  Example: AVERAGE_PRIOR( {Source System Balance (Reporting)}, '2', 'EUR', 'REC')

  Note:

  Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.

• Date: Returns a date value based on specified integer values for the year, month and day. For example, this function creates a value of type Date, so DATE(2018, 5, 31) would convert to May 31st 2018. This can then be used in the Date Difference function, to pull the difference in days between a date, such as an end date, and this specific date.

  DATE(<Year>, <Month>, <Day>)

  You can also use PERIOD START, PERIOD END, or PERIOD CLOSE dates when creating a calculated data attribute. For example, if you wanted to compare the Closed Date on an Adjustment transaction to the PERIOD END date.

• Date Difference: Returns the difference in days, hours minutes, or seconds between two dates. For Date1 and Date 2, the values 'TODAY' and 'NOW' can be used, which denote the current date (with no time component) and date-time, respectively.

  Valid types are DAYS, HOURS, MINUTES, SECONDS.

  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. Extract Text/Text Location uses the value of 1 for the first character.

  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')))
  

• Instring: Returns the index of the substring within the value.

  INSTRING(<Value1>, <Value to Search>)

  Example: INSTRING({Name}, 'a')

• Length: Takes a text value as a parameter and returns an integer which is the number of characters in the text.

  LENGTH('Value') returns 5, and LENGTH({Name}) would return the number of characters in the name of the object. If the value is empty/null, the calculation will return 0.

  Use the calculation with SUBSTRING to extract the last 4 characters of a text value.

  Example: 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>, <To Currency*>, <Rate Type*>, <Rate Period*>)

  Example: MAX_PRIOR( {Source System Balance (Functional)}, '6', 'CAD', 'REC', 'CURRENT')

  Note:

  Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.

• Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.

  MIN(<Value1>, <Value2>,<ValueN>)

  Example: MIN( TRANSLATE( {Source System Balance (Entered)}, 'CAD', 'REC'), TRANSLATE( {Source System Balance (Functional)}, 'CAD', 'REC'), TRANSLATE( {Source System Balance (Reporting)}, 'CAD', 'REC') )

• Minimum Prior: Returns the minimum value over the prior X periods.

  MIN_PRIOR (<Value>, <Number of Periods>, <To Currency*>, <Rate Type*>, <Rate Period*>)

  Example: MIN_PRIOR( {Source System Balance (Functional)}, '6', 'EUR', 'Simplified')

  Note:

  Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.

• 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, so 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.

  Note:

  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. If the Number of Periods Prior is omitted, it is assumed to be the value 1.

  PRIOR(<Value>, <Number of Periods Prior*>, <To Currency*>, <Rate Type*>, <Rate Period*>)

  Example: PRIOR( {Source System Balance (Entered)}, '1', 'EUR', 'rec', 'prior')

  Note:

  Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.

• 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>, <To Currency*>, <Rate Type*>, <Rate Period*>)

  Example: SUM_PRIOR( {Source System Balance (Reporting)}, '3', 'EUR', 'REC')

  Note:

  Parameters with an asterisk, *, are required if the value is a balance-type attribute like Source System. If the value is a normal value (like a numeric attribute), then the * parameters should be omitted.

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation. The Rate Period parameter must be either 'CURRENT' or 'PRIOR', which means when converting the currency, use the current period or prior period rate types.

• Text Location: Returns the index of the substring within the 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( {Source System Balance (Entered)}, 'EUR', 'Accounting')

  Note:

  Rate Type must be a valid rate type on the system, or the value 'REC', which means use the rate type assigned to the reconciliation.

• 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>)