1. Administering Financial Consolidation and Close
  2. Setting Up Task Manager
  3. Managing Task Manager Attributes
  4. Defining Calculation Attributes

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.

To define a calculation attribute:

  1. On the Home page, click Application, and then click Task Manager.
  2. Click the Attributes tab on the left.
  3. Click New, and then on the Properties tab enter a Name for the attribute, and then select an attribute Type.
  4. Select the Calculation option.
  5. On the Calculation tab, select a 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 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)

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

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

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

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

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

    ROUND <Attribute>, <Decimal Places>)

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

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

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