Union

Scenario 3

Use Union to calculate Total Operating Expense.

• Generally, metrics are derived based on individual drivers.

• Total Operating Expense is a metric calculated from individual drivers such as IT Expense, Tools and Equipment Expense, and Consumable Expense.

• Total Operating Expense = IT Expense + Tools and Equipment Expense + Consumable Expense

• Total Operating Expense is the formula node with the above formula, with input nodes being IT Expense, Tools and Equipment Expense, and Consumable Expense.

• The formula can use multiple nodes with a combination of mathematical operands like +.

Scenario details

• Formula to calculate: Total Operating Expense = IT Expense + Tools and Equipment Expense + Consumable Expense

• IT Expense, Tools and Equipment Expense, and Consumable Expense are captured/maintained at the same level of detail by the GL Account.

• The module defined for the Input template to maintain both of the Input node values: Finance Planning.

• After calculation, the result will be posted to the same level of detail by GL Account.

Input Node

• Node1: IT Expense
• Module: The Finance Planning module is created with the below definition:
  • (Rows = GL Account, Columns = Time, Filter = Version)
• Relation to be used in Formula is Merge.

• Node2: Consumable Expense
• Module: The Finance Planning Module is created with the below definition:
  • (Rows = GL Account, Columns = Time, Filter = Version)
• Relation to be used in Formula = Union.
• Union Metric:IT Expense.

• Node3: Tools And Equipment Expense
• Module: The Finance Planning Module created with the below definition:
  • (Rows = GL Account, Columns = Time, Filter = Version)
• Relation to be used in Formula = Union
• Union Metric:IT Expense

Expected Output Node

• Formula Node: Total Operating Expense
• Module: The same Finance module can used as the template and the level of details are the same.
  • (Rows = GL Account, Columns = Time, Filter = Version)
• Formula to be used = IT Expense + Tools And Equipment Expense + Consumable Expense

Create Formula

There are 2 parts to creating the formula node in the Value Driver tree.

Create Value driver

1. To create a new value driver tree, click on the Drive menu:

   • Under Calculations, click on + and select Rules to open the popup for capturing value driver details:

   • Description: Total Operating Expense

   • Action Type: Select Value Driver

   • Model: Finance Planning

     

2. Click on + Add Rule on the level 2 header to open the node creation pane.

General Tab

• Name: Total_Operating_Expense

• Description: Total Operating Expense

• Method: Formula

• Module: Finance Planning

• Formula: 'IT Expense' + 'Tools And Equipment Expense' + 'Consumable Expense'

1. Select the field member tagging to post the result of the calculation.

   Click on the filter icon on the right of each field and make relevant selections:

   • Cost Center: Same as Source
   • Version: Same as Source
   • Time: Same as Source
   • GL Account: KPI0025: Operating Expense
   • Dataset: Same as Source

   Note: By default, the output put is posted as "Same as source", even if left unselected for fields.

   

Input Tab

1. Click on Input Tab and open node: IT Expense

   • Module: Finance Planning

   • Relation: Merge

   • Fields member selection: Click on the filter button on the right of each field:

   • Cost Center: Context

   • Version: Context

   • Time: Context

   • GL Account: 6510020 - IT Expense

   • Dataset: AU2030 - Finance

   • Uneye the GL account field

2. Next on Input Tab itself, open Input node: Tools And Equipment Expense:

   • Module: Finance Planning

   • Relation: Union

   • Union Metric: IT Expense

   • Fields member selection: Click on the filter button on the right of each field:

   • Cost Center: Context

   • Version: Context

   • Time: Context

   • GL Account: 65100200 - Tools And Equipment

   • Dataset: AU2030 - Finance

   • Uneye the GL account field

3. Next on Input Tab itself, open Input node: Consumable Expense:

   • Module: Finance Planning

   • Relation: Union

   • Lookup Metric: IT Expense

   • Fields member selection: Click on the filter button on the right of each field

   • Cost Center: Context

   • Version: Context

   • Time: Context

   • GL Account: 65100210 - Consumable Expense

   • Dataset: AU2030 - Finance

   • Uneye the GL account field

   

4. Click on Save.