Calculating Active and Terminated employees For each month

Question

I have been scouring the forums and trying to figure this out for hours. It seems so simple, yet putting the workflow together has proven to be quite hard for me.

I have a data set with EE_ID, Status, HireDate,TermDate. The table doesn't have history. It is just the current EE's status along with their hire date and term date(if they have one).

I'm able to figure out the # of terminations for each month by calculating termed EE's based on status. If status = terminated, then term count goes up by 1 for that month based on their term date. A terminated EE needs to be counted as active employee AND a terminated employee IN the month they were terminated.

In the attached Sample.

There are 10 Employees records

In April 2018 there were 2 terminations. So the active count for April 2018 = 10(Total EEs that were at some point active in April) and the term count = 2

This leaves 8 employees going forward

There were 3 terminations in Feb 2019. So active count for Feb 2019 =8(Total EEs that were at some point active in Feb) and the term count = 3

So, when the workflow is run again the future, those counts should remain the same for their respective months.

Thanks for any help on this!

Craig

Sample.csv

adamorse · Accepted Answer

I've attached a workflow that might give you some ideas. What it does is output a table with a record for every month in which at least one employee was active, a count of active employees for that month (including any terminated during that month), and a count terminated during that month.

I'm not entirely sure I know what your end-goal is, but maybe this will help you figure out how to wrangle the data!