Examples: Using Calculated Attributes

Service Administrators can use custom attributes to implement solutions that adhere to their reconciliation policy.

This topic discusses a few common examples of creating calculated attributes and using them in rules.

Example: Transaction Drop Off

The Preparer needs Balance Explanations or Adjustments to drop off reconciliations if the Close Date has passed. This example creates a calculated attribute and applies it in a rule which will stop the transaction from carrying forward if the attribute calculation goes above zero.

If a Close Date has been entered on the transaction, the attribute Close Date Drop Off can be created by using the Date Difference and Add Month calculation functions. This attribute will put the Close Date back a month and will calculate the date difference between your new attribute and the current period end date in days. This attribute is then applied to a Copy Transactions from Prior Reconciliation rule to ensure that Adjustments will not be carried forward into the period that the Close Date is in.

The calculated attribute Close Date Drop Off has the following settings:

  • Type: Number
  • Total: Sum
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: DATE_DIFF(EDATE({Close Date}, -1), {Period End Date}, 'DAYS')

This attribute can then be applied to the Transaction Detail in the format with no view access to any roles.


Example of using a calculated attribute to drop off transactions

A Copy Transactions from Prior Reconciliation rule can then be created which will drop off the transaction if the attribute day difference calculation is greater than 0.


Example of using a calculated attribute to drop off transactions

Example: Stop Auto Reconciliation

The Service Administrator wants to stop auto reconciliation from occurring if a reconciliation has been auto reconciled two months in a row. This can be implemented by creating a custom attribute and applying it to the required rule.

Create a calculated attribute named AutoRecCheck with the following settings:

  • Type: Text
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: SUM_PRIOR(IF_THEN_ELSE({Auto Reconciled} = 'Yes', 1, IF_THEN_ELSE({Auto Submitted} = 'Yes', 1, 0)),2)

Then, apply this calculated attribute to a rule with criteria that meets your requirement. In this example, the calculated attribute is used in the filter criteria of a rule, as shown in the following screenshot.


Rule that uses a calculated attribute to stop auto close of reconciliations

Example: Calculate Period Variance and Require an Explanation

The Service Administrator wants Preparers to enter an explanation when the variance between periods fluctuates over a specified percentage.

This example creates a custom attribute of type Number and a calculated attribute that computes the variance between two periods. Although this example uses Variance Analysis, the values can also be interchanged for Account Analysis methods.

Create a calculated attribute Variance % with the following settings:

  • Type: Number
  • Total: None
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: (TRANSLATE({Source System Balance (Functional)}, 'USD', 'Accounting') - TRANSLATE({Variance Period Balance (Functional)}, 'USD', 'Accounting'))/TRANSLATE({Source System Balance (Functional)}, 'USD', 'Accounting')

Next, create a custom attribute named Variance Description with Type set to Text.

Create a rule using both the calculated attribute and the text attribute. In the Filter Definition, set the filter criteria as shown in the following screenshot.


Example of using attributes with a rule to enforce variance explanation.

Example: Track a RAG Score

This example creates a rule to change the reconciliation RAG (Red, Amber, Green) score based on the transaction RAG score.

Create two custom attributes, LineRAG and RecRAG, of type Text. The LineRAG must be placed in the Format transactions and the RecRAG must be placed on the Format attributes tab.

Rules are then built in the following order to set the RecRAG based on the transaction LineRAG. The order of the colors in the rules is important. Red needs to be the last rule so that if any transaction in your set of items has a red color, the RecRAG will be red. For instance, if you have three transactions and all LineRAGs are green, the RecRAG will be green. But if you have two green LineRAGs and one red LineRAG, the third rule will be run last and will apply a RecRAG of red since one of those transactions had a red LineRAG.


RAG score format

A rule to set the RecRAG to Green, Amber, or Red is created using the example below. Three rules must be created for Green, Amber, and Red.


RAG score rule

Rules must be run periodically during the day to ensure that the RecRAG is updated with the latest information on the reconciliation. This can be done manually or by using the EPM Automate importReconciliationAttributes command with Rules = SET_ATTR_VAL.

See importReconciliationAttributes in Working with EPM Automate.

Example: Auto Submit and Approve a Reconciliation if there is No Source and Subsystem Activity

The Service Administrator wants to set up a rule that auto submits reconciliations if there is no source system activity or subsystem activity.

To implement this, create a calculated attribute to calculate the change in subsystem balance between periods. The calculated attribute named Subsystem Activity has the following settings:

  • Type: Number
  • Total: Sum
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: TRANSLATE({Subsystem Balance (Functional)}, 'USD', 'REC') - PRIOR( {Subsystem Balance (Functional)}, 1, 'USD', 'REC', 'PRIOR')

Then, apply this calculated attribute to a rule that has a criteria that meets your requirement. The following screenshot applies the calculated attribute to filter criteria of a rule.


Example of using a calculated attribute to auto-submit reconciliations

Example: Calculate the Current Month in the Quarter

The Service Administrator wants reconciliations to show the current month of the quarter, such as M1, M2, and M3.

Create a calculated attribute named Period Number with the following settings:

  • Type: Text
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: 
    IF_THEN_ELSE(MONTH ({Period End Date})=1, 'M1', 
(IF_THEN_ELSE(MONTH ({Period End Date})=2, 'M2',
(IF_THEN_ELSE(MONTH ({Period End Date})=3, 'M3',
(IF_THEN_ELSE(MONTH ({Period End Date})=4, 'M1',
(IF_THEN_ELSE(MONTH ({Period End Date})=5, 'M2',
(IF_THEN_ELSE(MONTH ({Period End Date})=6, 'M3',
(IF_THEN_ELSE(MONTH ({Period End Date})=7, 'M1',
(IF_THEN_ELSE(MONTH ({Period End Date})=8, 'M2',
(IF_THEN_ELSE(MONTH ({Period End Date})=9, 'M3',
(IF_THEN_ELSE(MONTH ({Period End Date})=10, 'M1',
(IF_THEN_ELSE(MONTH ({Period End Date})=11, 'M2',
(IF_THEN_ELSE(MONTH ({Period End Date})=12, 'M3',
'recon not found')))))))))))))))))))))))

Example: Require an Action Plan if the Transaction has Aged More Than 90 Days

The Service Administrator wants Preparers to create an action plan if a transaction has aged more than 90 days. In the rule, create filter criteria as shown in the following screenshot.


Example based on transaction age

Example: Preventing the Entering of Transaction Dates from a Future Period

Service Administrators want Preparers to only enter transaction dates, such as Balance Explanations, in the current period. Create a rule in the format that looks at the Age of the transaction.


Example - transaction dates

The above example will not work if action plans are being used because an action plan date calculates the age differently. When action plans are being used, create a custom attribute named Transaction Age with the following settings:

  • Type: Number
  • Total: Sum
  • Calculation Type: Scripted
  • Select Display to User
  • Calculation Definition: DATE_DIFF({Transaction Date}, {Period End Date}, ‘DAYS’)

Then, apply this calculated attribute to a rule after adding it to the Transaction Detail.


Example