Move values from flat list to hierarchical list

Hello, Everyone!

Please, help me to solve the issue.
I have a Flat list with the -1 and 1 values.
I have a Hierarchical list consisted from the same code from the flat list but in the hierarchy. There are more than 15 levels.
I want to assign every -1 or 1 from the flat list to every element in the hierarchical list.
The value for summary levels should be taken from the flat list, not from kids.

Thanks
Art

Hierarchical_list_108768.PNG

Flat_list_108768.PNG

Accepted answers

All comments

AntonMineev

Hi @Artem.shchaulov!
This is not an easy question. And I think you are already aware of all the basic restrictions (LOOKUP with a formula, etc.).

My suggestion is as follows:
1. Create a technical list (via import). The name is the same as that of FLAT List. Code: FLAT Code & 1 / -1.
2. You find element of TECH Flat list with FINDITEM.
3. You get his code.
4. Through the formulas of the text, you get from there 1 / -1 at the end of the code.
5. Everywhere there summary = formula.

The example in the picture is attached. This is not a perfect description, but I think the general approach is clear.
When writing formulas, one should follow the Anaplan practices for working with text. For demonstration purposes, I have not made perfect formulas.

Снимок экрана 2021-05-19 в 15.07.11.png

rob_marshall

@Artem.shchaulov

Question, is the hierarchal list a numbered list? If so, the display name should be list formatted to the flat list instead of being formatted as Text. In having it list formatted to the flat list, you can simply do a lookup to the values in the module dimensiionalized by the flat list (1 and -1).

Rob

AntonMineev

@rob_marshall , but this same option won't work for the not-list (summary) elements (CFX/PLX in my picture)?

We can pull up 1 / -1 through LOOKUP and mapping, but we cannot do this for non-list elements. Or do you suggest a different approach (without LOOKUP)?

rob_marshall

@AntonMineev

You have to use the lookup and the best way to do it is to use the method I suggested, having the Display Name being list formatted to the Flat list, so we have a built in link. For the summary pieces, you would use the Summary function of Ratio.

From the looks of it, @Artem.shchaulov is attempting to do signage for a Chart of Accounts. I would follow this article and video.

Artcile: https://community.anaplan.com/t5/How-To/Changing-the-Sign-for-Aggregation/ta-p/53812

Video: https://community.anaplan.com/t5/How-To/Modeling-Video-Series-2-Signage-Convention/ta-p/93236

The trick with a numbered list is the display name, don't use text, but use list formatted Display Names.

Rob

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…