Lookup on Parent Elements

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.

Data by Product Source.JPG

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")

User Product Selections.JPG

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)

Result with Lookup Used.JPG

Lookup Formula.JPG

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.

Lookup Basis Test.JPG

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:

SUM Formula.JPG

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.

Accepted answers

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.

Screen Shot 2019-03-22 at 2.23.41 PM.png

Screen Shot 2019-03-22 at 2.23.52 PM.png

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.

Screen Shot 2019-03-22 at 2.24.05 PM.png

Thanks,

LipChean

All comments

LipChean_Soh

Hi bdeaton,

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.

Screen Shot 2019-03-22 at 2.23.41 PM.png

Screen Shot 2019-03-22 at 2.23.52 PM.png

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.

Screen Shot 2019-03-22 at 2.24.05 PM.png

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

Quick Links

Can you help?

Unanswered questions

Importing Ragged Hierarchies
Hello - I have a 2 level ragged hierarchy (yes, it's a chart of accounts), that for reporting purposes is used in 4 different models (yes, it's a P&L). What is the best way set this to allow one to be the "source of truth" and then import between the four models without creating actions that include ignores? It seems like…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…