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
ABC	John	8-Mar-19	28-Sep-20
XYZ	John	22-Apr-19	28-Sep-20
ABC	Michael	22-Feb-23	29-Mar-24
XYZ	Michael	25-Jan-23	8-Jul-24

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:

https://community-forums.domo.com/main/discussion/62912/calculate-number-of-employees-over-time-using-employee-hire-termination-dates

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.