Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

Replicate "Running Total In" from Excel Pivot into Alteryx

Highlighted
Atom

Hello Experts,

 

Newbie here. I would like to automate some of my headcount reports from Excel to Alteryx, but am having trouble replicating a pivot. A watered down version of my data is below. Basically when a contractor starts, they have 1 in WF FTE and the start date goes in date. When the contract ends, they get a -1 and the exit date goes in date. Date reporting is always the first of the month from date. 

 

In my Excel pivot, I do a sum of WF FTE but it's show value as "Running Total In." Because I don't have an entry for every month, I don't know if I can use the Running Total Tool. I suspect some upstream formulas, summaries, transpose and/or crosstabs are needed, but not sure.  Any help is much appreciated! 

 

Excel Pivot:

clipboard_image_1.png

Data:

PersonWF FTEDateDate ReportingComment
Contractor A11/2/20191/1/2019Entrance
Contractor A-15/5/20195/1/2019Exit
Contractor B15/22/20195/1/2019Entrance
Contractor B-111/8/201911/1/2019Exit

 

Pivot Field Settings:

clipboard_image_0.png

 

Thanks,

 

Erin

 

Alteryx Partner

Hi @E_Miller 

 

Something like this should do the trick.  Essentially, you can use the multi-row tool to pull the exit date from the subsequent row to the current row and use the Generate Rows tool to create a record for the months in between start data and exit date.  I added a record to the file for a contractor who has a start date but has not yet exited so we can create a scenario for treating that use-case as well.  Let me know if this works for you.

 

Replicate Running Total In.png

Highlighted
Atom

Thank you very much John! I think this will work! Appreciate it!

 

Erin

Labels