4 Replies Latest reply on Jul 15, 2016 2:23 PM by David Li

    Calculation for employee turnover rate

    Tejal Narkar

      Hi Guys,

       

      I am trying to calculate employee turnover rate. I also want to give the user a functionality to filter on year|quater|month.

      The formula for calculating the turnover rate is:

       

      Turnover(quarter q1) = [number of separation in Q1(Jan, Feb, Mar)] / [average headcount from start to end of Q1(Jan, Feb, Mar, Apr)]

      Turnover(Month Jan)= [Number of separation in Jan]/ [avg headcount in feb and mar]

       

      I have attached the sample excel sheet. Using the example in the excel sheet the turnover rate for Q1 will be as follows:

       

      Population Files:

      Separation File:

       

       

       

      Turnover rate Q1=3/(avg(5+4+5+6))

      Similarly, if the user selects Feb month the turnover rate should be : 1/(avg(4+5))

       

      Any Suggestions on how this could be calculated.

       

      Regards,

      Tejal

        • 1. Re: Calculation for employee turnover rate
          Tejal Narkar

          Can anyone help me with this problem. Or suggest any changes in the data file to get the numbers. As I am new to tableau any suggestions and help will be appreciated .

          • 2. Re: Calculation for employee turnover rate
            David Li

            Hi Tejal, in the "Separation Population" sheet, are the Employee numbers in there IDs or counts?

            • 3. Re: Calculation for employee turnover rate
              Tejal Narkar

              Sorry for the mistake. Just updated the sheet. They were employee IDs.

              • 4. Re: Calculation for employee turnover rate
                David Li

                No problem. Here are some recommendations on some steps you could take to find the monthly turnover rate:

                 

                1. Set up your data so it's all in one table. Each row corresponds to a single month for a single employee (as it is now in the separate tables).
                2. Add a column/field that indicates when an employee leaves during the month. For instance, the column could have values of 0 except when an employee leaves, in which case it has a value of 1.
                3. Load the data into Tableau and then set up your sheet so that you have discrete months in the columns. That is, there should be a column for Jan, one for Feb, one for Mar, etc. Remember that your sheet layout is the primary thing that determines how the underlying data is aggregated. In this situation, everything is grouped up by month, so if you were to drag [Number of Records] onto the Text marks box, you'd see the number of employees in any given month.
                4. Now, to calculate the average headcount over a quarter, you're going to need to use table calculations. If you've never used them before, here's a list of resources compiled by one of the giants upon whose shoulders we stand, Jonathan Drummey. Basically, you need to set up the table calculation to take the WINDOW_AVERAGE() of the number of records, computing along the date, restarting every quarter. Give this a shot and let me know if you can't figure it out.
                5. Calculate the number of turnovers per month by counting the number of rows where the turnover column = 1. This is very simple; just sum up that field! That's why we made it 0 and 1 to begin with.
                6. Divide that number by the average you calculated in Step 4, and voila! You've got the turnover rate per cell, which happens to be the turnover rate per month.
                  • If you want to make it turnover rate per quarter, you have at least a couple of options.
                    • If you don't care about seeing the monthly numbers, you actually don't need the table calculation in step 4 at all. You would calculate the average head count by just putting quarters into the columns, summing up the number of records, and then dividing by 3. Simple! Since the data gets aggregated at the quarter level if you do that, there's no need for window table functions. And you could get the quarterly turnover rate by just summing up on the turnover field and dividing that by the average I just mentioned.
                    • If you do care about seeing the monthly numbers, you can do another table window calculation, but this time with WINDOW_SUM().

                 

                There are a lot of nuances involved in doing table calculations that I'm sure you'll discover when you play with this. Let me know if you have questions, and good luck!