SUM Parent Item in Hierarchical list

MarkTurkenburg_*8288
MarkTurkenburg_*8288
in Modeling

Hi Team,

 

I have an issue whereby I can't SUM from 1 Hierarchical list to 2 different Lists....let me explain with an example 

 

I have Source Module which is dimensionalised by #Employee (the parent item of #Employee is Region L1)

I have a Target module which is dimensionalised by Clone Region & Budget Owner

I have mapping for both #Employee to Budget Owner and Region L1 to Clone Region

 

My thought is that it would be a simple SUM for both Budget Owner & Clone Region dimensions but it errors when you enter both as a SUM but you can do either independently. I'm assuming this has something to do with the fact that #Employee roles up to Region L1?

 

Source Module

IMG1.PNG

Mapping for Clone Region

IMG2.PNG

 Mapping for Budget Owner

IMG3.png

 

Target Module - SUM by Clone Region

IMG4.PNG

 

Target Module - SUM by Budget Owner

IMG5.PNG

 Target module error when both SUM functions are included

 

img6.PNG

 

 

 

Tagged:

Best Answer

  • anikdas
    anikdas
    Answer ✓

    Hi @MarkTurkenburg 

    The independent aggregation on region works fine as Employees get automatically rolled up to Region L1 and then using the mapping, you can convert it into Clone Region. But when you are trying to aggregate across Clone Region and Budget Owner, you are using a formula based aggregation for "Budget Owner" and hence it does not auto-aggregate on Region L1.

     

    If you can create a property against Employee list, which links to Clone Region, you can then refer that property to do the aggregation.

     

    Region L1: PARENT(ITEM(Employee))
Clone Region: Clone Region Mapping.Clone Region[LOOKUP: 'Region L1']

Aggregation Formula:
'Source Data - Employee'.Cost[SUM: 'Budget Owner & Clone Region Mapping'.Budget Owner, SUM: 'Budget Owner & Clone Region Mapping'.Clone Region]

Answers

  • paolovm
    paolovm

    i wouldnt think its related to region as my understanding is that clone region would be entirely delinked from the original region/hierarchy.

     

    something i would test: would it work if you store both line items used in the sum formula in the same module? maybe creating 2 staging line items with both mappings in the source module?

  • MarkTurkenburg_*8288
    MarkTurkenburg_*8288

    Thanks @anikdas 

     

    Yes I was effectively trying to SUM off 2 different hierarchy levels. It works fine now that I have created mapping for #Employee to Clone Region and hence both SUM's are looking at the #Employee hierarchy 

     

    Mark

Categories