Hi Serena, I'm looking at your data and have a few questions.
Does Termination Date of 1/1/1900 mean "not terminated" or termination date unknown? Also, I am seeing pretty many termination dates before hire dates, what should be done with these?
In your sample screenshot above, I assume in the 12 month trend, you wish to see the rolling 6 month retention rate as of the end of each month. I'm not sure what QTD and YTD means in this context, though. Rather than 6 month rolling, is it that it should show "of employees hired YTD, how many are retained YTD", and same with QTD?
I tried to take a shot at your issue but I found some inconsistencies in the data, well, at least I didn't understand why there are many records within a same Employee ID.
I tried many approaches but got different numbers and I think it has to do with the same ID and many records.
Took a bit longer than I thought, but hey ... we all get there in the end.
I used only the data from the '201901' snapshot. You may need all of the data but i think it's works better with one block.
Am using the 'Scaffold' method. Using a separate sheet of dates to join to your data.
I ran this all past my HR person and he said the same thing ... Scaffold, before I showed him the workbook.
Attached is the workbook, using similar calcs to what we use here (Hires, Terminations etc).
Hope it helps.
295363.twbx 1.1 MB
Hi, Jennifer. Thanks for taking the time to review my question. Termination Date of 1/1/1900 means "not terminated." Also, the instances where Hire Date > Termination Date is a mistake on my part while trying to disguise the data. I will try to post a new attachment that cleans that up.
Hi, Adolfo. Thanks for taking the time to review my question. There are different types of activity denoted in the Type Cr field to flag activity associated with an Emplid for that month's snapshot, for example HC or MOVE. The Term and Hire fields look at these activity codes and flag the activity accordingly. Does that help shed some light on why the duplicates exist?
I see. The thing is, I was trying to use the Hire and Terminated date
fields to get the results you want but having multiple records (EmpID) for
these fields makes it even more difficult, I thought that there was just
one record per employee. Is there a way to filter the data to get just one
record per EmpID or no filters are allowed?
On Mon, Feb 11, 2019 at 8:10 PM Serena Sutherland <