Defining Calculation Attributes

Calculation attributes enable you to add logical and mathematical functions to derive values for attributes. They can be embedded within other calculated attributes to produce complex functions. Calculated attributes are read-only. They can only be evaluated against Schedules.

Schedules: You can add any Calculation and the calculation is evaluated, regardless of whether or not it was explicitly assigned to the task.

Templates: You cannot add a custom attribute that has the Calculation option selected as a column or a filterable attribute.

When administrators add attributes to the Attributes sections in the Actions dialogs, they are viewable by workflow users. Administrators can restrict access to certain roles by setting the access to Do Not Display. For example, for calculated attribute XYZ, an administrator could set the access to Viewer: Do Not Display so that the XYZ attribute is not displayed for users with only the Viewer role.

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

When you create an attribute and select the Calculation option, a Calculation definition section is displayed.

Calculation Type

You can select from the following calculation types. The available types are determined by the Attribute Type (Text, Integer, and so on).

  • Assign Value to List—Assign custom values to a List type attribute

  • Assign List To Value—Assign custom list values to the values of a different attribute. Only available for List type attributes.

  • Conditional—A conditional calculation (If – Then – Else)

  • Scripted—A free-form scripted calculation. Only available for attributes of type Integer, Multi-line Text, Number, or Text.

The following table lists the Calculation types available for each Attribute type.

Attribute Type Assign Value to List Conditional Scripted Assign List to Value
Date        
Date/Time        
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    

Calculation Definition

The Scripted Calculation type enables you to enter a free-form calculation equation. You can use the Add Attribute and Add Function.

  • Add Attribute—Select an attribute and insert the attribute into the Calculation Definition box at the location of the cursor. If the cursor is in the middle of a word or attribute, the word/attribute will be replaced in the definition. The script format adds brackets {} around the name of the new attribute.

    The only available attributes are Schedule attributes.

  • Add Function—Select a function and add the function to the Calculation Definition. The Function is added with placeholders for each parameter.

For example: Insert the DATE_DIFF function in the calculation definition:

DATE_DIFF(<Date1>, <Date2>, <Type>)

Then replace the placeholders with attributes:

DATE_DIFF( {Start Date}, {End Date}, 'DAYS')

Numeric and Date Functions

  • 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 years, months, 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( {Start Date}, 'TODAY', 'DAYS')

  • Day: Returns the day value of a date as an integer number

    DAY(<DATE>)

  • Extract Text: Returns the substring within the value, from the specified positions.

    SUBSTRING(<Value>, <Location>, <Length>)

    Example: SUBSTRING( {Name}, 4, 10)

  • If Then Else: Allows you 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( ( {Priority} = 'Low'), 1,IF_THEN_ELSE( ( {Priority} = 'Medium'), 2, IF_THEN_ELSE( ( {Priority} = 'High'), 3, 0)))

    Example: IF_THEN_ELSE( ( {Priority} = 'Low'), 'Good' IF_THEN_ELSE( ( {Priority} = 'Medium'), 'Better'. 'Best') )

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

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

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

  • Lowercase: Returns the value in lower case

    LOWERCASE(<Value>)

    Example: LOWERCASE ({Task Code})

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

    MAX <Value1>, <Value2>, <ValueN>)

    Example: MAX( {Scripted Substring Loc a}, {Scripted Substring Loc s}, {Scripted Substring Loc t} )

  • 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( {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( {Scripted Substring Loc a}, {Scripted Substring Loc s}, {Scripted Substring Loc t} )

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

  • 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*>, <To Currency*>

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

  • Round: Returns the value rounded to the decimal places specified

    ROUND <Attribute>, <Decimal Places>)

    Example: ROUND( ({Scripted Substring Loc t} / 7), '4')

  • Sum Prior: Returns the sum of a value over the prior X periods.

    SUM_PRIOR(<Value>, <Number of Periods>, <To Currency*>

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

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

Calculation Validation

The following validation checks are performed when you save the Calculation:

  • The syntax of the Calculation is correct.

  • The Attributes and Functions specified in the Calculation exist.

  • Parameters to Functions are correct.

  • No Circular Loops exist.