Refer to Parent list
Hi, I'm new here and I'm not that experienced yet with Anaplan. My problem: I've created 2 lists. Prof_level5 tells me if it's a mortgage loans, consumer loan, professional loan or defaulted loan. Prof_level6 is a drill down of level 5, it contains for example more detail on the professional loans saying if it's an immo loan, car loan, equipment etc Prof_level5 is the parent of level 6. Now I'm trying to create a balance sheet report. In my aggregation module it's not possible to add both lists in the same pivot, so I guess that's because the one is defined as a parent of the other. The system knows they are linked? So I just added level 6 in my module. Now I'm looking for the correct formula to select 'equipment' from the level 6 list in combination with the item 'professional loans' from level 5. I need to be able to define in my formula what the parent item is, as 'equipment' belongs to 'professional loans' but also 'defaulted loans' in level 5. This formula will not do it, as it will sum as well the defaulted and professional loans, and I want to exclude the defaulted ones: Balance sheet aggregation.amount[SELECT: 'Prof_level6'.'equipment'] At the end I just want it to return the value (the outstanding amount) for this combination to report in my balance sheet. many thanks in advance, Sandra
Answers
-
Hi Sandra,
You can add two items from different levels of the hierarchy, and the syntax you've got in your formula is broadly the correct way to do this (although clearly it needs tweaking as it's giving you the wrong result).
But it's very hard to say what exactly is wrong without seeing the full list structure. It might help if you gave some screenshots of the lists and the modules showing dimensionality etc. You should show us the module you want to take the data from as well as the module you're writing the formula in.
Also, you say you want to define "equipment in combination with professional loans" but at the same time you've said that 'professional loans' is the parent of 'equipment', so it wouldn't make sense to add these two together (because it would double-count 'equipment'). Finally, you say that "equipment belongs to professional loans but also defaulted loans", but it's not possible for an item in Level 6 to have more than one parent in Level 5, so I don't understand this either.
If you can clarify these points, I'm sure someone will be able to give you the solution.
Pete0 -
Hi Sandra,
in addition to Peter wise comment, I would add the following ideas :
1°) in the Level 6 list, lease add a property "Prof_level5 ", formated on the Prof_level5 list, and having the formula : Parent(Item('Prof_level6').
By doing so you will have a centralized place whete you can find the parent for SUMs and LOOKUPs. You will save a lot of time.
2°) Yes adding level 6 to your module was the right solution.
3°) Peter in right we need perhaps more stuff to help you. But waiting for this, try to use [LOOKUP : Level6.Level5] where the Level5 is the property you have created in the Level 6 list, instead of SELECT. But I am not sure I understand correctly what you are aiming for from which starting point
Please come back to us with further info.
Kind regards.
Michel.
0 -
Hi,
Thanks for the reply's! Here an overview of the list items as I would like to see them. As you can see for 'equipment4pro' in level 6 it belongs to 'professional loans' in level 5 as well as to 'loans default'.
But when creating level6 it seems that it's not possible to create 2 times the same wording 'equipment4pro'. So I guess the only solution would be to change the wording for the default loans into 'equipment4pro default' for example so that it's a unique name.0 -
Hi Sandra,
in order to use duplicate names, you need to swicth the list to a Numbered List.
However, when you will swicth the list to numbered list, Anaplan will replace the item names with numbers : #1, #2, ...
In order to avoid loosing the existing names, you can take the following steps BEFORE your active the numbered list :
1°) Create a property in the list "Prof_Level6" : call this property "Description" or "Display Name" or "Equipment" or whatever you feel is roght for you.
2°) Format the property into TEXT
3°) Insert the following formula in the property formula : NAME(ITEM('PROF_LEVEL6'))
4°) Remove the formula: Anaplan wil keep the existing values of the names
Then you can swicth to the Main List windows and click "Numbered List", and thenselect the display as being the property you have created.
You can then reopen the list and insert new elements. Note than when you will insert new element in a numbered List, Anaplan will ask you for how many of them you want to insert : 1, 2, 5, 10.
In the grid mode, you can write the names in the property you have created.
Good Practice would be to create an Action Button CREATE, to publish this button on a dashboard for Managing Prl-Level6, and to crate a module which allow a non admin to manage the list.
Hope it can help you. Feel free to ask for further information.
Kind regards.
Michel.
0 -
Hi,
I forgott to mention than it is often recommended to have codes whe you are using numbered list.
If there are existing code, you can use them. Or create codes if there are nne.
Codes will allow you to import lists between models (Hub / production) more easely.
Kind regards.
Michel.0 -
Hi Sandra
You can just see if you can make them two seperate lists and use them in the module accordingly.
As at this scenario I dont think using numbered list would help you but again am not sure.0 -
Hi Sandra,
In addition to Harish comment, yes that's true, normal lists are all right if Prof Level5 is not the parent of Prof Level6. Then you can use two lists, not numbered, and add the two independant lists in your modules.
As often in Anaplan, several solutions are available. really depends on your business need.
To avoid displaying Not Allowed cominations (some prof Level6 not allowed for some Prof level 5) you may wish to use a filter module with boolean, or other possibilities (color formatting is not allowed).
Kind regards.
Michel.0
