Replicate "Running Total In" from Excel Pivot into Alteryx

[E_Miller]

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:

Data:

Person	WF FTE	Date	Date Reporting	Comment
Contractor A	1	1/2/2019	1/1/2019	Entrance
Contractor A	-1	5/5/2019	5/1/2019	Exit
Contractor B	1	5/22/2019	5/1/2019	Entrance
Contractor B	-1	11/8/2019	11/1/2019	Exit

Pivot Field Settings:

Thanks,

Erin

[john_miller9 · Accepted solution]

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.

[E_Miller]

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

Erin