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
Solved! Go to 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.
Thank you very much John! I think this will work! Appreciate it!
Erin