Sum and Lookup using 2 mapping tables

nathan.fowler
nathan.fowler
in Modeling

Hi All, 

I currently have a module with 2 lists with data that need to be in another P&L list via 2 mapping tables using a methodology.

 

Source Data dimensions: List 1, List 2, Time: Month

Target P&L dimensions: P&L List, Time: Quarter

 

This is the methodology:

  1. If List 1 Mapping Table.Methodology = Methodology List 1 then I want to take a sum of all the values for List 1 item (ie L4 Leaf 1: I want L4 Leaf 1 to be sum of all values in Leaf 1, Leaf 2, Leaf 3 and apply it to the tagged P&L view selected.  In this case Revenue)
    1. L4 Leaf 1 = Methodology List 1
    2. L4 Leaf 1 = Revenue
    3. Take Total List 2 = 370 and apply to Revenue in P&L Consolidation
  2. If List 1 Mapping Table.Methodology = Methodology List 2 then I want to take that line and move data at the List 2 to the tagged P&L view (ie: List 1 mapping table = L4 Leaf 3 then I want data in the data table for the P&L view of Leaf 1, Leaf 2, Leaf 3 to be applied to the P&L items found in List 2 mapping table.  Leaf 1 value =  Revenue, Leaf 2 value = Expenses, Leaf 3 value = Expenses.)
    1. L4 Leaf 3 = Methodology List 2
    2. Navigate to P&L view in List 2 mapping table
    3. Leaf 1 = Revenue
    4. Leaf 2 = Expenses
    5. Leaf 3 = Expenses
    6. L4 Leaf 3, Leaf 1 = 105 and should be applied to Revenue in Consolidation P&L
    7. L4 Leaf 3, Leaf 2 = 110 and should be applied to Expenses in Consolidation P&L
    8. L4 Leaf 3, Leaf 3 = 115 and should be applied to Expenses in Consolidation P&L 
Tagged:

Answers

Categories