Map a list in the target module to line items in a source module

sliebermensch
sliebermensch
in Modeling

I have 2 modules one is for performing calculations the other is to report on the calculation results.  The line items in the calc module align with a list, not a line item subset, that the reporting module is dimensioned by.  There is a single line item in the, Amount, in the reporting module. One other twist is that I need to sum the values based on a line item in the calculation module that aligns with another list in the reporting module.

 

Is there a way to do this such that the formula for Amount is not a nested if statement?

 

Calc Module Dimensions:

SKU-Cost Center(Concatenated)

 

Calc Module Line Items:

Department(Parent of CC)

Advertising

COGS

 

Reporting Module Dimensions:

Accounts(Contains Advertising and COGS)

Department

Brand(Parent Hierarchy to SKU|CostCenter

 

Line Items in Reporting Module:

Amount

 

Any help would be appreciated

 

Tagged:

Best Answer

  • Joris
    Joris
    Answer ✓

    Hi Steve,

     

    Indeed you will need to use a Line Item Subset to accomplish this. Steps to take;

     

    1. Create a Line Item Subset based on your "Calc" module that includes the Advertising and COGS line items

    2. Create a new module called "Mapping" with the Line Item Subset as the only list and a single line item "Account" formatted as the "Accounts" list

    3. Create a new module called "Calc collect" with "SKU-Cost Center" and the Line Item Subset as list, and a single line item "Amount". This line item needs the COLLECT() formula to pull in the data from the "Calc" module.

    4. In your "Reporting" module you will now be able to retrieve the data from the "Calc collect" module using the mapping table. The formula should look something like this: 

     

    Calc collect.Amount[SUM: Mapping.Account, SUM: 'SKU-Cost Center'.Department]

     

     

    Please note that I assume you will have the Department as a property of the 'SKU-Cost Center' list (and not necessarily as a line item in the Calc module). The SUM by Brand will happen automatically as this is the parent of the 'SKU-Cost Center' list.

     

    Best,

    Joris.

     

Answers

Categories