How to implement "hardcoded" logic with best practice in following example

[email protected]

Hi,

 

I have the following challenge in modeling the target module. I need to aggregate data before calculation to account level 1, but there is an exception for certain accounts where I need to aggregate those for level 2. I have tried to create system account details where I flag those accounts which are exceptional accounts.

 

I have marked yellow lines which are exceptional accounts that should be aggregate in account level 2.

 

Thanks in advance,

 

Antti

[email protected]

I solved this in that way I created a separate module for this calculation. It works for me the best.

Yash1

Hi [email protected] ,

 

I am not sure if I understood your question completely.

There are 2 ways you can do this:

1. Create a parent child relationship. You can create 1 child for all other cases, whereas in your exceptional case, the parent will have 2 children.

2. You can create a mapping module, with 2 line items with type as Source list. Target list should be used as dimension in the module and then use a lookup function to aggregate.

 

Let me know if this helps. 

Thanks,

Yash 

Misbah

[email protected] 

 

Understood the requirement, but few clarifications

 

1. Is it Ragged hierarchy? I believe so since it is chart of accounts.

2. When you say Level 1 and Level 2 - What level is the account level?

 

However see if this helps

https://community.anaplan.com/t5/Best-Practices/Production-lists-and-ragged-hierarchies-logic/ta-p/33591

 

[email protected]

1. It is not a ragged hierarchy. Every account has a parent in level 2 and every each level 2 members have level 1. 

 

2. So there is Account flat is describing the accounts in lowest level. These accounts belong to account level 2. Account level 2 members parent is account level 1.

 

Misbah

[email protected] 

 

Still confused. As per your point 1  this is what I think it is

But your point 2 contradicts your point 1 and it states "So there is Account flat is describing the accounts in lowest level. These accounts belong to account level 2"? Are these Level 2 or Level 3?

[email protected]

This is not ragged hierarchy. I  tried to illustrate your question in the picture the attach.

 

Cell: J18 and K18 shows formula what I build in system module.

 

Red market closures illustrate the example of account hierarchy.

prabhu

Hi [email protected],

 

Can you answer my questions, because it would be helpful to investigate your problem.

 

1. What is the dimension of Source Module?

2. What is the dimension of Target Module?

 

You have used Account as a dimension in System module and a boolean formatted line item which marks exceptional account.

 

3. In system module If you mark an account as an exceptional account then what should be your output?

4. In system module If you din't mark an account as an exceptional account then what should be your output?

 

5. Why did you named a line item(Boolean) in system module as "Interconnection and roaming" instead you could have named it as "Exceptional".is there any link?

 

Thanks,

Sorna Raja Prabhu

[email protected]

1. I have two dimensions in the source module - Account Flat, which parent hierarchy is Account level 2, and Account level 2 parent hierarchy is Account level 1. There is cost center flat but this is not relevant for this one.

2. In the target module I'am using Account level 1 subset as a dimension.

 

3. The account details system module is just my imagination of how I can possibly solve the problem. I have understood that it is always better to use system module than using select in line formulas.

4. If an account is not exceptional then it can sum normally to Account level 1.

5. This is just naming conventing thing. It can be also an exceptional account.

 

The attach is my excel.