SUM Parent Item in Hierarchical list

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

Mapping for Clone Region

Mapping for Budget Owner

Target Module - SUM by Clone Region

Target Module - SUM by Budget Owner

Target module error when both SUM functions are included

Accepted answers

anikdas

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]

All comments

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?

anikdas

Hi @MarkTurkenburg

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]

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

Quick Links

Can you help?

Unanswered questions

Can Please explain the difference between a general list,a module,and action in Anaplan.
Can please explain why we create a general list ,a module,and Action ,and what purpose each one serves.🙂
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…