Lookup on Parent Elements

bdeaton

Got the idea I'm trying from here: https://community.anaplan.com/t5/Anaplan-Discussions/Lookup-on-Parent-elements/m-p/1478#M2397. But, we needed to change it up a bit as I need it to be user specific but it's not working. 

 

I created a very simplified version of what we are trying to do on a larger scale. 

 

I started with a data module that has information we need by product. Product is a list in the model and it has leaf levels and parents all in the same list.

Next, I created a user input module where each user will select what product they want to see. Some users may choose to see a rollup (i.e. "Product Group 1") while others want to just see a single leaf-level product (i.e. "Product A")

As expected, when I try to use just a straight LOOKUP formula, the results aren't right (0s are returned when a product rollup was selected by the user)

 

So, we did some research and found the community article I linked above and got the idea to use a SUM formula instead. I created a simple mapping line item that returns "refresh" for products that should be added up based on the users' selection. Refresh is the only  member of the list "refresh list" which we already had available in the model for other uses.

 

 

Then, my thought was to create a line item that included "refresh list" in the applies to and do a simple SUM formula. However, we are getting the following error:

 

Any ideas on getting this to work in a way that won't create a lot of space drain? We are purposely not dimensioning this module by product due to space considerations - the real module has several additional dimensions but trying to simplify to get down to the root of the problem that we are having.

LipChean_Soh

Hi bdeaton,

 

If the data is already in a module, i would enable the users to go to the report itself, and search for the relevant Product or Product Group. We're on a slippery slope when we start to build reports with slightly different and specific requirements.

 

I also noticed that you combined Product Group and Product into 1 list. I prefer splitting it into 2 lists connected via the 'Parent Hierarchy'. There might be a future scenario where i have to enter values at the Parent Group hierarchy. 

 

Having said that, please see if the following fulfills what you're looking for. I have minimized the 'users' column to protect the identity of the users.

 

Please note that you cannot choose 'Product' in the PG line item; nor can you choose 'Product Group' in the Product line item.

Line item 'Valid?' ensures only PG or Product is chosen.

 

Thanks,

LipChean

bdeaton

Appreciate the response!

 

We do allow users to go to the report, but it doesn't provide the variance analysis that our users need. The source module is already large enough and adding the necessary variance analysis is not possible. So, our thought was to create a separate report that can do the variance anlaysis based on user-selected time periods, scenarios, and products (and thus not require any of these three lists to be dimensions of the new report).

 

We were trying to avoid creating yet another product list (we have several and are trying to be careful of the maintenance requirements we are creating), but that is probably our best backup plan. What you have outlined makes sense and I think we could make that work. I was just hoping to avoid the new product list issue.

DavidSmith

I would add to what @LipChean_Soh  has said

It is best practice to use Composite lists rather than a combined list like you have here.

The specific issue does occur because we cannot lookup parent values in a combined list

Using a composite list, and a selection module such as the example would work like this:

 

David

 

bdeaton

Thank you both for the ideas and workarounds. I think that I'm going to put in an item on the idea exchange to allow us to do a SUM like I attempted to do (where the source was not dimensioned by "users" but the rule and target line items are). I think that would be a very cool feature - although I still don't really know why it doesn't work that way now.

 

I agree that compositie list is preferable, but in this case not ideal. We ultimately determined, after some further discussion with the requesting end-users, that a shorter flat-list will work and we will be able to dimension the module by that list instead of making it a user selection at all. 

g.ganivet

Hello,

 

If you have a Combined List with code defined like below :

Level	Code	Item
1	A	World
2	A_A	EMEA
3	A_A_A	FR
3	A_A_B	EN
2	B_B	ASIA
3	B_B_A	CHINA
2	C_C	AMERICA
3	C_C_A	USA

 and an INPUT module with the input data dimensioned by Combined List

You could set up a line item Result in a subsidiary view of your Selection module (by Combined List) :

         Selection is the user input selection (list, no dimension)

         Level is the level of user input selection (number, no dimension)

         Length is the length of each part code included separator (number, no dimension)

         Result = IF CODE(Selection) = LEFT(CODE(ITEM(Combined List)), Level * Length - 1) THEN INPUT.data[SUM: Select] ELSE 0