Best way to capture hierarchy tree and sum

[jessy_chow]

Hello, 

Attached is a sample of my data for help. 

Everyone employee reports to manager which then reports to a director and then reports to a VP. The positions are identified via a positionID. 

I am trying to populate this relationship in alteryx as well as getting the total of FTE per Cost Centre. (See spreadhseet for desired outcome)

I have demo-ed out what my desired outputs are in the attached excel sheet. 

I really hope this makes sense. I can try to clarify if needed. 

Thanks soo much! 

[caltang]

Question:

1. The hierachy is based on whoever is on the last row? So 1 reports to 2, 2 reports to 3, 3 reports to 4? Is this true for all scenarios or are there rules?
2. You say cost centre 10004 has 3 FTEs, but they all report to different cost centres? Unless they are parent-child cost centres?
3. What have you tried so far and can you give me more context?

[caltang]

Well I did a sample here with a lot of assumptions. 

If you can give different groupings and more scenarios, then it can be made more dynamic for you.

[angela852black]

That's great This is a really amazing which provides quality information.  peryourhealth

[jessy_chow]

Sorry -  I was trying to do a mockup of simple data but realized I caused confusion. 

Question:

1. The hierachy is based on whoever is on the last row? So 1 reports to 2, 2 reports to 3, 3 reports to 4? Is this true for all scenarios or are there rules? 
   
   No, the hierachy is based on the position ID. So in my previous data - Mary reports to Steve and Steve reports to Kelly and Kelly reports to Cole. So each position ID would correlate with who they report to and so-fourth. Just so happens in my mock data that it was whomever is in the last row. 

2.You say cost centre 10004 has 3 FTEs, but they all report to different cost centres? Unless they are parent-child cost centres? 

It is a parent-child relationship. So Cole's cost centre is the Top and then the rest would flow down. 

1. What have you tried so far and can you give me more context?

On my copy, I did multiple joins based on the position id and reporting id to determine the tree. 

I've attached a cleansed copy of my data to provide better context. 

[caltang]

What does FTE mean? I see some whole numbers and decimals. One more question: Are there dual reporting lines?

[WirkKarl]

Hi there!

Thanks for sharing your sample data and explaining the hierarchy — that really helps.

To capture the reporting structure (employee → manager → director → VP) in Alteryx, you might want to explore the Hierarchy tools, like the Find Nearest or use iterative macros to build the tree from the relationships.

For summing the total FTE per Cost Centre, a Summarize tool grouped by Cost Centre should do the trick nicely.

If you want, I can help put together a quick workflow or point you to some examples that handle similar hierarchical data. Just let me know!

Hope this helps and looking forward to your reply!

[jessy_chow]

The total FTE per cost centre with Summary in theory would work, however since I'm trying to capture the total FE's under the the Director's / VPs which consists of many different cost centres. I'm not entirely sure how to do it. 

Would I be able to see a workflow that you think would work?

Thanks! 

[jessy_chow]

FTE - full time equivalents, just means the number of employees. 

There is a position ID that reports to a position ID. 
So like Employee 101 reports to manager 102 
manager 102 reports to director 103 etc 

[caltang]

I see some as 0.63 FTEs... what does that mean?