Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #12: Creating an HR Hierarchy

GeneR
Alteryx Alumni (Retired)

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. You can find the solution to the previous challenge here.

 

For this challenge let’s look at creating a multi-level hierarchy from employee-manager data. As always there are several ways to do this challenge, I have designated it as an advanced challenge because there is an elegant way to solve it using iterative macros. The advantage to the iterative macro solution is that it becomes very dynamic. Other hard coded solutions would get you to the answer with this data, but if the depth of the hierarchy were to change, you would have to modify the workflow to support the change. It is a great example to see how iterative macros can make a workflow dynamic.

 

The use case:

 

An HR department wants to use Alteryx to quickly understand the reporting structure for employees across their organization.

 

The Input source contains 5 employees and an identifier that uniquely identifies the individual and the manager they report to.

 

The goal is to create a hierarchy field identifying each relationship between employee and manager(s). For example, a Director reports directly to the Vice President which is 1 level up. The Director is then 2 levels away from the CEO (in this data set). As a result the hierarchy identifier represents how many levels removed the employee is from management team they report into.

 

Give it a try, I look forward to your feedback.

 

UPDATED 2/16/2016:

The Solution has been included.

TaraM
Alteryx Alumni (Retired)

The solution to this one has been uploaded - a great example of an Iterative Macro!

Tara McCoy
alex
11 - Bolide

 

I worked this one the same way as the solution, except I added a step to sort the employees based on their hierarchy.  I wouldn't have wanted anyone reading the report to see that director is showing above manager since it was sorted alphabetically.  They may assume that the directors report to managers even though that is not what the text of the report shows.  See the non-macro workflow and sorted list below.  Thanks!

 

week12workflow.PNG

 

week12workflow2.PNG

GeneR
Alteryx Alumni (Retired)

Nice Alex, thanks!

siddhartha_s
7 - Meteor

Certainly a great solution - Now the next level is to build a org tree - using R may be - to make it visually intuitive. Any sugestion how can we get from here to something like:

 

Capture.PNG

GeneR
Alteryx Alumni (Retired)

Interesting idea @siddhartha_s  , I'm not enough of an R expert to write the code myself to to do this.  I am an Analysts that prefers using the pre-built R functionality in Alteryx.  I did have some fun playing with the network analysis tool, however it always generates a 2-way network so you don't get the nice end nodes like in your screen shot above.  I will reach out to the developers of the Advanced Analytics tools to see if they have any suggestions.

alex
11 - Bolide

I was able to get to this point.  Not exactly what you were asking for, but does visually represent who reports to who.

 

wk12diagram.PNG

GeneR
Alteryx Alumni (Retired)

I like it @alex

SeanAdams
17 - Castor
17 - Castor

Very similar solution to the provided one - it did bake my brain for a few hours tho' (first time using iterative macros for anything productive).

 

Main difference was the treatment of Depth and carrying the employees through the flow rather than separating & rejoining (I didn't use the built-in variables of the macro iterator engine thank you for that @GeneR)

 

Spoiler

MacroPic.png

estherb47
15 - Aurora
15 - Aurora

Getting the iterative macro to work was a lot of trial and error with the settings.