Count of Active Employees Month by Month

agolla2

I have a card to build which should show running graph of how many candidates had an active authorization at a particular office locations during a given month. It should be able to show historical running totals of employees that an office has and are they growing or shrinking.

So, I have dataset which has columns like office location details, employee names, from and to date when they are licensed to work from one office. An employee might have license or authorization to work in other office locations as well. So, how should I split the data by monthly as I have normal date format. Should I do anything particular in Magic ETL or can I directly write a beast mode calculation?

Can anyone please help me in this?

Example Data:

Company Name	Employee	From	To
Company Name	ABC	Employee	John	From	8-Mar-19	To	28-Sep-20
Company Name	XYZ	Employee	John	From	22-Apr-19	To	28-Sep-20
Company Name	ABC	Employee	Michael	From	22-Feb-23	To	29-Mar-24
Company Name	XYZ	Employee	Michael	From	25-Jan-23	To	8-Jul-24
Company Name		Employee		From		To

MarkSnodgrass

You can implement the LAST_DAY() function around all of the dates and that will group everything into monthly buckets. Do it before you do the join and it should get you what you want.

MarkSnodgrass

I answered a similar question on how to do this here:

Calculate Number of Employees Over Time (using employee hire & termination dates)

Kutsch

Nov 14, 2023

I have an employee data set with dates the employees were hired and then for some of them, when they were terminated- still unsure what to put in termination date column for employees that are still active.

I'd like to create a visualization that shows the number of active employees over time or at any given time.

I tried some stuff in beast mode, but wasn't quite getting it. In my head it seems like it should be easy, but I'm still struggling with it.

Any recommendations?

---

View Post

Let me know if that works out for you.

agolla2

Thanks for the response mark. I have tried this approach but ended up lot many rows in the final output dataset. Instead of daily granularity I am trying to do month wise.

MarkSnodgrass

You can implement the LAST_DAY() function around all of the dates and that will group everything into monthly buckets. Do it before you do the join and it should get you what you want.