Active Employees per Month

Follow

Steven Chen

November 25, 2019 20:06

Hi,

I'm encountering an issue when attempting to calculate active employees per month over the current year. 

We have a dimEmployee table that hosts a StartDate and EndDate column. An active employee per month is defined by StartDate <= LastDayOfMonth and EndDate > LastDayOfMonth.

The below graph is an attempt to use an "active" column which is defined by StartDate <= CurrentDate and EndDate > CurrentDate. This is an incorrect trend since we're attempting to find the headcount per month. We need the "CurrentDate" to be calculated as the last day of the month being calculated.



Can anyone help solve the issue without cross applying the the dimEmployee table and the fact table to create a new fact for every active contractor based on month?

Thanks,
Steven

0

• 

  Michael Becker November 25, 2019 20:20

  Hi Steven,

  this looks to me as data modelling thing, you need to get your data (through Transpose) to stage where table holds all possible dates and users combinations as lines (within date ranges specified). Soemthing liek that:

  user id | date

  1 | 1/1/2019

  1| 1/2/2019

  1| 1/3/2019

  and so on until you get all dates within start and end date. Then on front end you count user id, with filter setting it to last day of month. Hope that helps.

  Regards,

  Michael

  0

  Comment actions Permalink
• 

  Steven Chen November 26, 2019 00:32

  Hi Michael,
  
  Thanks for your input but we're actually trying to avoid creating date records for each user since we have a few hundred thousand employees which would in turn create an exponential amount of records. 

  Thanks,
  Steven

  0

  Comment actions Permalink

Please sign in to leave a comment.