Displaying and Summing Data in Hierarchies

[Johaon]

Dear all,

I am a beginner with this Alteryx, and this issue has been troubling me for a week. Basically, my goal is to sum different levels of hierarchies while maintaining the basic structure. I have attached a total of 4 files.

1. "Group account frame" is the main file. A parent account can have up to 10 levels. What I aim to achieve is the sum of different levels of the hierarchy, starting always from the lowest level account (which might be level 7 for some accounts and level 10 for others), without altering the structure. The difficulty I am facing is the inability to directly sum based on the hierarchy. For example, four highest-level accounts with hierarchy 1 are located in rows 2, 694, 1726, and 3144.

2. "Account balance" contains the account balances of subsidiary companies, and the "group AccID" column can be matched with the group account file.

3. The attached PNG file describes the basic aggregation method. The three balances with hierarchy 4 in the image should sum up to hierarchy 3. This, in turn, forms the basis for hierarchy 2 along with another hierarchy 3 located at a different postion in the list. Every "not lowest level" should be retrieved based on the "lowest level".

4. The column B in expected output file keeps the structure of the group level and includes the balances from subsidiary companies. AccID column should be used as an identification measure between the output and input files; allocation of the data set has been done on the basis of the AccID. For example, "1311122000-5023000"

I have tried various methods in the last few days, such as combination from filters, joins, multi-row formulas, macros, etc. However, the results are not satisfactory. I do not know how to dynamically identify and sum different hierarchies while preserving the existing structure. I hope I have expressed my dilemma clearly enough.

I have completed data cleaning and reconciled the accounts prior to this. Unfortunately, I haven't made much progress in terms of displaying the data, so I apologize for not having a helpful workflow to share.

Do you have any suggestions? Any help is greatly appreciated!

Thank you! 

[aatalai]

have you tried the summaries tool grouping by different higherachies etc and summing the relevant feild?

[Johaon]

Hi,

I have found a solution. I created a simple example to help me establish a workflow. The account hierarchy here goes up to a maximum of 4 levels. The goal is to insert subsidiary accounts under their corresponding parent company accounts and perform hierarchical summation. The expected output is also included in the attachment. My workflow looks quite messy, and I believe a macro would be helpful in this case, although it currently seems too complex for me. I hope this can be of assistance to others facing a similar issue. Any suggestions are highly welcome!

(Please forgive me that some explanations are wrote in German)

[Johaon]

Hi,

I think I can't handle it this simply here. It's more like a tree structure, and I must maintain the overall structure of the tree when aggregating. Summing up on the same level would disrupt the structure.

[aatalai]

This could help with the macro https://community.alteryx.com/t5/Learning-Paths/Advanced-Certification-Learning-Path/ta-p/660137