Selective Parent to Child Rollup

CommunityMember118291

Hello Everyone.

Is there any way from which i can control what all members in a hierarchy rolls up to their parent level.
Example- I have 2 lists L1 and L2. L1 is parent of L2.

L2 list has a property called status which can be Approved,Declined,Proposed etc.

I need to provide a control on the dashboard where in a user can select the status. Based on that status, all the L2 members having that status should roll up to L1 rest should not.

 

Is there a way that we can control such roll ups. Also this needs to be user specific.

anirudh

Hi,

 

Yes you can do  this with the help of IF ELSE formulas

1. Create a module with dimension of L2 and User, and another module for summary at L1 and User

2. Create 3 line items; first line item is the number formatted line item for which you want to control the rollup. Second line item is where the user inputs the Status (Approved, declined etc) and the last line item with the formula

 

IF Status = Status.'Approved' THEN 'Line Item 1' ELSE 0

 

3. Link the last line item to the L1/User dimensioned module. This will ensure that a particular L1 for a particular user will roll up only if all L2 members' status is set to Approved

 

Let me know if this works

 

Regards,

Anirudh

einas.ibrahim

@CommunityMember118291 

Do you want the L1 value to literally be the aggregation of L2 based on the selected status or you can just get the correct L1 based on the selected status (Value showing in the hierarchy vs. calculated in a different line item)

 

There are a few ways to aggregate a list (SUM, AVERAGE, RATIO,.....) but none of them can simulate what you are asking for. Think about it this way, you can't have 3 different values of the same L1 (one for each status Approved, Denied, Proposed) Anaplan would be confused.

However, if you don't want the roll-up to work as for native lists - because it's really not- then you can easily do that. Consider the screenshots below

This is the raw data module

This is how it'd work from a user's perspective
 

I know you also want this to be user-specific. That's the easy part.

 

If that's something you can work with let me know and I can walk you through the steps/send you the blueprint along with the user-specific implementation 

CommunityMember118291

Hi @einas.ibrahim,

I was also able to roll up to L1 level as you have shown in the screenshots but when it comes to user specific, I am facing issues. 
Do i need to make both of my modules (L1 and L2) user specific or it can work only by making L1 as user specific because using user list has a good impact on space. 

Also i need to show this rolled up value at L1 level not at L2 level.

anirudh

Hi,

Try to follow my approach with the User dimension mixed in.
Unfortunately, as you need selective rollup, user dimension has to be at L2 level....

Regards,
Anirudh

CommunityMember118291

Hi @anirudh ,

I tried your approach and it worked but, I have around 20 such line items that needed to roll up, can't create a formula driven line item for each of them, it will just keep adding space to my model and I have to consider that too.

And also if i will be having around 60-70 users in model, this will just take a lot of space. Is there any way to achieve this efficiently and to not have an impact on space? 

anirudh

Are all 60-70 users going to be using this dashboard?
If not, consider creating a dummy user list with just the users that are going to need access and then add this to the L2 and L1 modules and remove the Users list
Then enable selective access on the dummy user list, and provide write access to only the users that will be using the dashboard

Let me know if you need more details

Regards,
Anirudh