Forum Discussion

Vantage111's avatar
Vantage111
Frequent Visitor
3 years ago

Summarizing Employee Numbers by date

I have a table that has an employee ID, a date when they joined the company (join_date) and a date when they left the company (leaving_date). An employee is still a member until the leving_date is no longer a null.

 

I am trying to create a new table that will contain the following information:

The end of any Year/Month/Quarter number of leavers in the period, number of joiners in the period and the total number of employees still in the business.

 

Sample from existing table:

 

I have made some attempts at creating the new table, this is about the closest i've come to getting it to work, pretty sure there is a reasonably simple fix.

 

 

Regards - and thanks in advance....

Need Help

3 Replies

    • Vantage111's avatar
      Vantage111
      Frequent Visitor
      3 years ago

      Alexis

      Thank you, I had spotted the error and also another two that I will put below for completeness.

      Changed the MAX(Employee[join_date]) as in the previous version anyone joining after the last leaver would not register.

      Also added the OR employee[leaving_date] = Blank() to include those employees that have not left.

       

       

  • Vantage111's avatar
    Vantage111
    Frequent Visitor
    3 years ago

    So....still need to work out out the other two elements, number of Leavers and Joiners in a given period.

    With the help of AlexisOlsen and my brain defugging, I have a column that gives me the TOTAL employees on agiven date,