Multi Level Hierarchy with Totals

[agilleland17]

Hello, 

I am trying to do some data prep in Alteryx designer for a product hierarchy.

This is what the current data set looks like:

This is what I need the final data set to look like:

I need it completely automated and dynamic so it could handle as many levels as needed. 

Thanks!

a

[jamielaird]

Hi @agilleland17 

Can you explain the logic behind your target data structure?

I see where 20, 15 and 5 come from (sum of Total wrapped up the dimensions in L1 and L2) but 10 and 5 don't seem to follow the same logic, and the layout is a little confusing.

Perhaps if you could put some context around what the numbers mean that will help clarify.

In any case I think your solution is going to be achievable by transposing the data and summarizing with the right grouped fields.

[rafalolbert · Accepted solution]

Hi @agilleland17,

This is what i have, i don't think you can fully automate this.

For every level/layer you calculate aggregates and union it all together at the end retaining order top to bottom:

Thanks,

Rafal

#Excuse me, do you speak Alteryx?