How to Add a Formula in Validations

To add and clear a formula, do the following:

1. Navigate to Consolidation to open the Consolidation Control Panel

2. Click Validations under Processes. The Validations grid appears.

   

3. Click Add (or Edit or Copy). The dialog to add, edit, or copy a Validation appears.

   

4. Click the Formula tab.

5. Build a formula by performing the following steps:

   1. From within Members and Functions, drag and drop members and functions into the Left Hand Side and Right Hand Side boxes as necessary.

   2. Select an operator from the Operator drop-down list.

   3. Optional. Click √ to check the syntax of the formula. A status message will appear below the Left Hand Side and Right Hand Side boxes indicating whether or not the syntax is correct.

6. Clear a formula in the Left Hand Side box or Right Hand Side box by performing the following step:

   1. Click Clear Formula associated with the Left Hand Side box or Right Hand Side box.

      

7. Click Save.

How to Manage Formulas

You manage formulas when adding, copying, and editing a Validation. The following elements are available to manage a formula.

• Left Hand Side and Right Hand Side —The left-hand and right-hand portion of the formula can include members, functions, or both. You can also click inside the box to edit members and functions directly. A copy-paste is also allowed inside the box.

  If the data entered in the Left Hand Side and Right Hand Side returns no data, the application validates as if the data is equal to zero (no data). After the Validation, the result may be a pass or fail based on each case. For example, if LHS(0)=RHS(0), the status is passed.

• Operator —Contains various operators to build a meaningful formula. Select an operator for the formula. Use a combination of mathematical operators to build full expressions, including multiple components, if desired.

  Example formulas are:

  • Sum(Acct1,Acct2,Acct3)=Acct4

  • (Acct1+Acct2)-(Acct3)=Acct4

  • Acct1*70%=Acct2

  • Acct1>0

  • Acct/Acct2 = Acct3/Acct4

When a formula-based Validation is defined using an equal to (=), less than or equal to (<=), or greater than or equal to (>=) operator, the absolute difference between the LHS and RHS is displayed for your convenience. For example, if account 1 is not equal to account 2, the difference between the accounts is displayed.

• Check Syntax —Click √ to verify whether the formula is syntactically correct.

• Clear Formula —Click Clear Formula to clear the formula. The Clear Formula option is available for both the Left Hand Side and Right Hand Side boxes.