Calculating Values for Date/Time and Numeric Fields

Transition field calculations provide a way to collect metrics as users move items through the process using Date/Time and Numeric fields. You can set calculations for transitions in workflows in SBM Composer, or you can configure a calculation for a transition in a project in SBM Application Administrator.

To calculate values for Date/Time and Numeric fields in transitions in projects:

  1. From the Projects view, select a project, and then click Details.
  2. Select the States/Transitions tab.
  3. Select a transition, and then click Details.
  4. Select the Fields tab.
  5. Select a Numeric or Date/Time field, and then click Details.
  6. Select the Allow Override check box.
  7. Select the Set Value to Calculation option.
  8. Select one of the following options for determining when the calculation should be performed:
    • Calculate Before Form

      Select to perform the calculation to occur before the Transition form opens. Calculate Before Form is selected by default.

    • Calculate After Form

      Select to perform the calculation after the Transition form is submitted.

    • Calculate Before & After Form

      Select to perform the calculation to occur both before and after the Transition form is submitted.

  9. Select the following calculation options:
    • First Operand

      Using the guidelines in About Operand Fields and Operator Selection Lists, specify a constant value, field, or date/time keyword to use as the first operand in the calculation. This will be filled with the current value of the first operand. The field selected from the list cannot be the same field as the field being edited, a field that could cause a recursive calculation, a deleted field, or a field in the Not Used section.

    • Operator

      Select a valid operator for the calculation, using the guidelines and tips in Calculating Values for Date/Time and Numeric Fields.

    • Second Operand

      Using the guidelines in About Operand Fields and Operator Selection Lists, enter a second constant or a valid field. Valid fields for the second operand are dependent on the field type, the first operand, and the operator. If the first operand or operator causes the second operand to be invalid, the second operator is changed to a valid constant. The second operand cannot be the field being edited, a field that could cause a recursive calculation, a deleted field, or a field in the Not Used section.

  10. Select one of the following options for empty operand fields:
    • Are Invalid

      Select to require users to provide values for fields used as operands for the calculation before the transition can be completed.

    • Skip Calculation

      Select to allow users to complete the transition without providing values for fields used as operands for the calculation. The calculation is skipped if values are not provided.

    • Treat as Zero

      Select to perform the calculation and treat empty values in fields used as operands for the calculation as zeros.

  11. To add the calculation to the field's current value, select Add Calculation to Current Value. This enables you to increment the current value or to calculate the total of the calculation and the current value. This option is only available for Numeric fields and Date/Time fields set to record elapsed time.
  12. Save your changes.

Sample Calculation

This example explains how to calculate how long help desk calls are in a queue before a support representative begins working the item. For this example, you would create two Date/Time fields with the following properties in SBM Composer:
Date/Time Field Name Properties
Assigned To Support Rep
  • Style – Date/Time
  • Default Value – Now
  • Attributes – Read only
Time In Assigned
  • Style – Elapsed Time (Calculate Days and Show Seconds selected)
  • Attributes – Read only

Then, for a "Begin Work" transition that support representatives execute when they begin working on a ticket, set the calculation for the Time In Assigned field to:

Last State Change Date

– (minus)

Assigned to Rep

When support representatives execute the "Begin Work" transition, the amount of time that has passed before work began on the item is recorded in the Time in Assigned field. You can then create reports that reflect this data.

Tips for Creating Calculations

To ensure that you receive correct data from the calculation, set the field for which the calculation is set as read only and place it in the Hidden section.

Calculations do not work for system Date fields (Submit Date/Time, Close Date/Time, Last State Change Date, Last Modified Date) in a transition calculation for which the date will be calculated. Instead, use the "now" keyword, which gives you the same calculation.

The None operator is useful if you want to transfer the value of another field to this field during this transition.

If the calculation could result in a value that cannot be stored in the type of field, either a round or truncate version of the operator must be used. For example, if you define the calculation for a Numeric integer field and you want a division calculation, you must choose the round or truncating division operation. This is because the division can result in a fraction that can't be stored. With rounding operators, any fraction greater than or equal to a half will be rounded up to the next whole integer or date, while fractions less than a half are dropped. With truncating operators, the fractional part of the number is ignored. For example, seven divided by four would calculate to one with the truncating division and two with the rounding division.

About Operand Fields and Operator Selection Lists

The following table displays the valid choices for the operand fields and operator selection lists for a Numeric or Date/Time field using the calculation feature. The valid choices are based on the type of transition field being edited.

Field Type

1st Operand Constant

1st Operand Field Types

Operators

2nd Operand Constant

2nd Operand Field Types

Elapsed Time

Elapsed Time

Elapsed Time

None, +, -

Elapsed Time

Elapsed Time

Elapsed Time

Elapsed Time

Elapsed Time

*, Trunc/, Round/

Float

Numeric Int, Single Select, Summation

Elapsed Time

Elapsed Time

Elapsed Time

Trunc*, Round*, Trunc /, Round /

Float

Numeric Float

Elapsed Time

Date/Time

Date/Time

-

Date/Time

Date/Time, Date

Elapsed Time

N/A

Date

-

Date/Time

Date/Time, Date

Date/Time

Date/Time

Date/Time

None, +, -

Elapsed Time

Elapsed Time

Date/Time

N/A

Date

None, +

Elapsed Time

Elapsed Time, Time

Date/Time

N/A

Date

-

Elapsed Time

Elapsed Time

Date

Date

Date/Time, Date

None, Trunc +, Round +, Trunc -, Round –

Elapsed Time

Elapsed Time

Time

Time

Time

None, +, -

Elapsed Time

Elapsed Time

Time

N/A

Date/Time

None (assigns time portion of date/time)

N/A

N/A

Numeric Int

Int

Numeric Int, Single Select, Summation

None, +, -, *, Trunc/, (integer math), Round/

Int

Numeric Int, Single Select, Summation

Numeric Int

Int

Numeric Int, Single Select, Summation

Trunc +, Trunc -, Trunc *, Trunc /, Round +, Round -, Round *, Round /

Float

Numeric Float

Numeric Int

Float

Numeric Float

Trunc +, Trunc -, Trunc *, Trunc /, Round +, Round -, Round *, Round /

Int or Float

Numeric Int/Float, Single Select, Summation

Numeric Float

Int or Float

Numeric Int/Float, Single Select, Summation

None, +, -, *, /

Int or Float

Numeric Int/Float, Single Select, Summation

Numeric Float

Int or Float

Numeric Int/Float, Single Select, Summation

*

N/A

Elapsed Time (converted to hours)