6 Replies Latest reply on Feb 11, 2019 7:22 PM by Adolfo Hernandez

    Employee Retention

    Serena Sutherland
    Serena Sutherland Feb 11, 2019 10:14 AM

      I am trying to set up a metric to track employee retention and answer the question, "Of people hired in the last 6 months, what percent are still here?" An example would be:  As of December 2018 100 people have been hired between July 2018 and December 2018.  As of December 2018, 10 of those 100 people hired the last 6 months have terminated.  This means our retention rate is 90% (100-10)/100 or 1-(10/100) as 90% of our hires over the last 6 months are still here.

       

      My stakeholder would like to see the rate by month for the last 12 months as well as a Quarter-to-Date value with YoY comparison and Year-to-Date value with YoY comparison.  A sample view is below.

      I have been working on these calcs off and on for over a month and cannot get it to work.  Please help!

       

      I've tried to attach a workbook with sample data, version 2018.1.1.  This is a tricky one to share on the community due to the sensitive nature of my data set.  I've had to condense and disguise the data significantly--hopefully I've provided enough for you to work with.

       

      I look forward to hearing your ideas & suggestions for removing this long-standing blocker.  Thanks in advance.

      • 198 Views
      • Categories: Calculations, Dates & Times
      • Tags:
        • 1. Re: Employee Retention
          Jennifer VonHagel
          Jennifer VonHagel Feb 10, 2019 6:43 PM (in response to Serena Sutherland)

          Hi Serena, I'm looking at your data and have a few questions.

           

          Does Termination Date of 1/1/1900 mean "not terminated" or termination date unknown?  Also, I am seeing pretty many termination dates before hire dates, what should be done with these?

          In your sample screenshot above, I assume in the 12 month trend, you wish to see the rolling 6 month retention rate as of the end of each month. I'm not sure what QTD and YTD means in this context, though. Rather than 6 month rolling, is it that it should show "of employees hired YTD, how many are retained YTD", and same with QTD?

           

          Thanks,

          Jennifer

          • 2. Re: Employee Retention
            Adolfo Hernandez
            Adolfo Hernandez Feb 10, 2019 7:25 PM (in response to Serena Sutherland)

            Hello Serena,

             

            I tried to take a shot at your issue but I found some inconsistencies in the data, well, at least I didn't understand why there are many records within a same Employee ID.

             

            I tried many approaches but got different numbers and I think it has to do with the same ID and many records.

             

            Adolfo

             

            • 3. Re: Employee Retention
              Chris Hopkinson
              Chris Hopkinson Feb 10, 2019 9:49 PM (in response to Serena Sutherland)

              Hi Serena,

               

              Took a bit longer than I thought, but hey ... we all get there in the end.

              I used only the data from the '201901' snapshot. You may need all of the data but i think it's works better with one block.

              Am using the 'Scaffold' method. Using a separate sheet of dates to join to your data.

              I ran this all past my HR person and he said the same thing ... Scaffold, before I showed him the workbook.

               

              295363_Image.PNG

               

              Attached is the workbook, using similar calcs to what we use here (Hires, Terminations etc).

               

              Hope it helps.

               

              Chris

              • 4. Re: Employee Retention
                Serena Sutherland
                Serena Sutherland Feb 11, 2019 9:24 AM (in response to Jennifer VonHagel)

                Hi, Jennifer.  Thanks for taking the time to review my question.  Termination Date of 1/1/1900 means "not terminated."  Also, the instances where Hire Date > Termination Date is a mistake on my part while trying to disguise the data.  I will try to post a new attachment that cleans that up.

                • 5. Re: Employee Retention
                  Serena Sutherland
                  Serena Sutherland Feb 11, 2019 9:40 AM (in response to Adolfo Hernandez)

                  Hi, Adolfo.  Thanks for taking the time to review my question.  There are different types of activity denoted in the Type Cr field to flag activity associated with an Emplid for that month's snapshot, for example HC or MOVE.  The Term and Hire fields look at these activity codes and flag the activity accordingly.  Does that help shed some light on why the duplicates exist?

                  • 6. Re: Employee Retention
                    Adolfo Hernandez
                    Adolfo Hernandez Feb 11, 2019 7:22 PM (in response to Serena Sutherland)

                    Hi Serena,

                     

                    I see. The thing is, I was trying to use the Hire and Terminated date

                    fields to get the results you want but having multiple records (EmpID) for

                    these fields makes it even more difficult, I thought that there was just

                    one record per employee. Is there a way to filter the data to get just one

                    record per EmpID or no filters are allowed?

                     

                    Thanks

                    Adolfo

                     

                    On Mon, Feb 11, 2019 at 8:10 PM Serena Sutherland <