Lookup - Same Module

Scenario 1

Use Lookup to calculate Sales from Units & Price, when Units & price have the same level of details

• Generally, metrics are derived based on Individual drivers

• Gross sales is a metric calculated from Individual drivers such as Units Sold & Average price.

• Gross Sales = Units Sold * Average Price

• Gross Sales is Formula node with the above formula with Input nodes- Units Sold & Average Price

• Formula can use multiple nodes with the combination of mathematical operands like *, /, +, -

sample formulas can be -

Formula	Details
A * B	A & B are Input or formula nodes
A * B * C	A,B, C can be formula or Input nodes
(A + B ) * C	A,B, C can be formula or Input nodes
(A * B) /C	A,B, C can be formula or Input nodes
(A * B) / (C * D)	A,B, C, D can be formula or Input nodes

Scenario details

• Formula to calculate: Gross Sales = Units Sold * Average Price

• Units Sold & Average Price are captured/maintained at the same level of details -

  • by account, by product & by customer

• Module defined for Input template to maintain both the Input node values - Gross Sales

• After calculation, the result will be posted to the same level of details

  • by account, by product & by customer

Input Node

• Node1 - Units Sold

• Module - Module Gross Sales created with the below definition

  • (rows = account, product, customer, columns = time, filter = version)

• Relation to be used in Formula = Merge

• Node2 - Average Price

• Module - The same module Gross Sales can be used as the template & level of details are the same.

  • (rows = account, product, customer, columns = time, filter = version)

• Relation to be used in Formula = Lookup

• Lookup Metric - Units Sold

Expected Output Node

• Formula Node - Gross Sales

• Module - The same module Gross Sales can be used as the template & level of details are the same.

  • (rows = account, product, customer, columns = time, filter = version)

• Formula to be used = 'Units Sold' * 'Average Price'

Create Formula

There are 2 parts of creating the Formula node in Value Driver tree

Part 1 - Create Value driver tree

Step 1: To create a new value driver tree, Click on Drive menu

• Under Calculations, click on + & select Rules to Open popup for capturing value driver details

  • Description - Gross Sales Calculation

  • Action Type - Select Value Driver

  • Model - Revenue Planning

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

General Tab

• Name: Gross_Sales

• Description: Gross Sales

• Method: Formula

• Module: Gross Sales

• Formula: 'UnitsSold' * 'AvgSalesPrice'

Step 3: Select the field member tagging to post the result of the calculation

Click on the filter Icon on the right of the each of fields and make relevant selections

• Customers: Same as Source

• Products: Same as Source

• Company: Same as Source

• Version: Same as Source

• Time: Same as Source

• Revenue Account: R10100 - Gross Sales

Note - By default - the output put is posted as "Same as source", even if left not selected for fields.

Input Tab

Step 4: Click on Input Tab and Open node - UnitsSold

• Module: Gross Sales

• Relation: Merge

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

  • Company: Context Member

  • Version: Context Member

  • Time: Context Member

  • Revenue Account: R10010 - Units Sold

Step 5: Next on Input Tab itself, open Input node - AvgSalesPrice

• Module: Gross Sales

• Relation: Lookup

• Lookup Metric - UnitsSold

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

  • Company: Context Member

  • Version: Context Member

  • Time: Context Member

  • Revenue Account: R10020 - Avg Sales Price