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

Set text encoding format for anaplan connect script upload
Hi, I would like to specify in an Anaplan Connect script that UTF-8 text encoding should be used for the file I am uploading. Does anyone know the command for this, or another way to make this happen? Thanks!
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…