Employee count by month

Randyb
Randyb Member
in Magic ETL

I've been asked to build out a card that shows employee count by month, this seems easy enough until you try and factor in the termination date.

What is expected is to show in each month the total active employees, so if employee A is hired on Jan 1st and terminated Feb 15th he would show as a count of 1 in January and a count of 0 in Feb. and then being able to sum or count the total employees active in that month.

I can start this as of the beginning of this year so could filter out all employees with a terminationdate prior to Jan 1st which could give me a starting value for January and then add in all employees with a Month(Hiredate) =1 and Termination date is null, but not exactly sure how to keep this going from month to month.

Pretty sure somebody must have run across this before, any help will be appreciated.

Thanks

Randy

Best Answer

  • Randyb
    Randyb Member
    Answer ✓

    So I kept working on this since it appeared as though it was stumping the community at large and I have come up with a solution that works if anyone is interested.

    The Beast Mode below checks to see if the Month Current date - "the months back you are going" falls into last year by producing a negative value. It also checks to see if it produces a 0 result.

    Both results generate adding the result to 13 if the number is a negative and 12 if it equals 0.

    This gives me a month into the prior year such as 12, 11, 10 etc.

    This allows me to span from one year to the next:

     

    Case when Month(CURRENT_DATE())-4 <0 then
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day )
    Then 1 else 0
    End)
    when Month(CURRENT_DATE())-4 =0 then
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day )
    Then 1 else 0
    End)
    Else
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day )
    Then 1 else 0
    End)
    End

     

    Hope this may help other people needing to have calculations spanning a year period.

    Thanks

    Randy

Answers

  • Randyb
    Randyb Member

    So I tried using this Beast Mode in a modified form and it works until I cross into the prior year, not sure what type of case statement will qualify the year and make the appropriate adjustment.

    Here is the orginal Beast Mode that gives me last months active Employee Count:

     

    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day )
    Then 1 else 0
    End)

     

    If I modify the the Beast Mode (One for Each Month) by putting -2, -3 etc after the current_Date() it works until it crosses into last year, so it would look like this:

     

    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE())), interval -1 day )
    Then 1 else 0
    End)

     

    If I modify it like this for last year this works, just need to figure out how to tell it to use this version instead of the previous types if it falls into last year:

     

    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE()-1)), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE()-1)), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()-3),'-1-',YEAR(CURRENT_DATE()-1)), interval -1 day )
    Then 1 else 0
    End)

     

    This is going to result in quite a large Beast Mode but it would work if I can bridge the year, or any better suggestions, perhaps this will help anyone see exactly what it is I'm trying to accomplish.

    Hopefully this update will result in some replies.

    Thanks

    Randy

  • Randyb
    Randyb Member
    Answer ✓

    So I kept working on this since it appeared as though it was stumping the community at large and I have come up with a solution that works if anyone is interested.

    The Beast Mode below checks to see if the Month Current date - "the months back you are going" falls into last year by producing a negative value. It also checks to see if it produces a 0 result.

    Both results generate adding the result to 13 if the number is a negative and 12 if it equals 0.

    This gives me a month into the prior year such as 12, 11, 10 etc.

    This allows me to span from one year to the next:

     

    Case when Month(CURRENT_DATE())-4 <0 then
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(13+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day )
    Then 1 else 0
    End)
    when Month(CURRENT_DATE())-4 =0 then
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(12+MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())-1), interval -1 day )
    Then 1 else 0
    End)
    Else
    Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
    +
    CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE())-4,'-1-',YEAR(CURRENT_DATE())), interval -1 day )
    Then 1 else 0
    End)
    End

     

    Hope this may help other people needing to have calculations spanning a year period.

    Thanks

    Randy

  • njest12
    njest12 Member

    Coming to this very late. Appreciate the detail @Randyb and the BeastMode that you found. With that beastmode, can you tell me what you used for your X and Y axis and what chart type you used? What did your source data look like? I'm trying to do something similar and would love to get your approach. Thanks!

Categories