12 Replies Latest reply on Aug 5, 2016 12:36 PM by deepa p

    How to calculate  avg employee headcount to get the trunover

    deepa p

      Hello all,

      I know this has been discussed in previous threads but i m finding it difficult to replicate solutions to solve my problem.

      Can some one please help me with the calculation on how to  get the avg headcount of employees to calculate the turnover. i have two date fields Hire date  and Term date.

      1) trying to get the avg headcount of employees.

      2) how can i achieve all the active employees prior to 2016.

      3) turnover by BU based on the date.

       

      I have attached a packaged workbook includes a mock up data .Any help would be appreciated!.

       

       

        • 1. Re: How to calculate  avg employee headcount to get the trunover
          Stoyko Kostov

          Hi Deepa,

           

          I will repeat my suggestion from this post: Using two date fields to create trend across time

           

          Basically, I recommend a cross self-join. Since Tableau always requires field to join on, we need to add a dummy column to your spreadsheet and set it to a constant. Then you join on this column to achieve cross-join.

           

          Change both hiredate and termdate to dates.

           

          To get the headcount, create a calculated field named Headcount with the following definition:

           

          IF([hiredate (Sheet11)] <= [Hiredate] AND ([termdate (Sheet11)] >= [Hiredate] OR ISNULL([termdate (Sheet11)])))

          THEN 1

          ELSE 0

          END

           

          Drag hiredate to columns and headcount to rows. This gives you the headcount over time.

           

          To get the data by BU, drag BU (Sheet 11) to Color.

           

          I'm attaching the spreadsheet with the Dummy column and the workbook I produced.

           

          Hope this helps - let me know if you have additional questions.

          1 of 1 people found this helpful
          • 2. Re: How to calculate  avg employee headcount to get the trunover
            deepa p

            Hello Kostov,

             

            Thank you very much for the solution. For my current requirement  i wont be able to do cross join. Is there any other workaround with out modifying the data can we achieve the same results?

             

            The Avg headcount is  required to calculate the turnover. i am able to get the total active employee headcount and total terminations.

             

            Active Headcount

            count(If status='Active' then empid  end)

            Terms count

            count(if not isnull(termdate) and status='Inactive' then emplid end)

            Turnover

            [terms count]/[active headcount]

            The only problem is how do i get avg employee headcount for the date range selected to get the  for turnover analysis.

            • 3. Re: How to calculate  avg employee headcount to get the trunover
              Stoyko Kostov

              Sorry Deepa, I'm not aware of a way to do this without modifying the data.

               

              I think cross self-join is needed because you want a time dimension that is orthogonal to what you have in the table: that dimension would allow you to compute headcount/turnaround at ANY time in the past.

               

              If you are OK with computing headcount/turnaround at a FIXED time in the past, you wouldn't need a join. You would be able to achieve this using a parameter, and then allow your users to change this parameter.

               

              If you need the former: Let me know why you are unable to modify your data, and I can try to mitigate your issues.

               

              If you need the latter: Let me know if you would like me to help you with that.

               

              Thanks!

              • 4. Re: How to calculate  avg employee headcount to get the trunover
                Stoyko Kostov

                Actually, I had an idea: since you want to calculate headcount per BU, you can join on BU.

                 

                Still requires a self-join, but you don't need the extra Dummy field and thus you don't need to modify your data.

                 

                Attaching the solution. It looks slightly different from the one I previously attached for 2 reasons: it doesn't show data points when the BU had 0 headcount, and it shows data points only when there was a change within the BU. But the relevant numbers are the same.

                 

                Hope this helps! Let me know if there's any other way I can help.

                2 of 2 people found this helpful
                • 5. Re: How to calculate  avg employee headcount to get the trunover
                  deepa p

                  Hello Kostov,

                   

                  Thank you very much for the elegant solutions. i really appreciate your time.Just want to do this by not changing the underlying data. Now i am attaching a new file with eff date as one of the new column in excel file. I need to calculate the turnover based on the parameter selection and I can use effdate for that.

                  Can you please guide me how i can get that solution by using a parameter.

                   

                  Requirement:

                  period selection  for end user.(say User picks a date form Jan1st 2015 to Dec 31 2015)

                   

                  I should be able to get the turnover ratio for that period.

                   

                  Turnover ratio : no of terminations or no of employees left the company/ no of active employees.

                  suppose I  have Jan1st 100 employees

                     and June 30th 150 employee then I need to add (100+150)/2=250/2 (this is my avg turnover) not sure how to get this in tableau.

                  Turnover Ratio: no of terms/Avg of employee active headcount (this should be dynamic based on the parameter date selection).

                   

                  I really appreciate your help in this.

                   

                  Thank you.

                  • 6. Re: How to calculate  avg employee headcount to get the trunover
                    deepa p

                    Hello Kostov,

                     

                     

                    Thank you very much for the elegant solutions. i really appreciate your time.Just want to do this by not changing the underlying data. Now i am attaching a new file with eff date as one of the new column in excel file. I need to calculate the turnover based on the parameter selection and I can use effdate for that.

                    Can you please guide me how i can get that solution by using a parameter.

                     

                    Requirement:

                    period selection  for end user.(say User picks a date form Jan1st 2015 to Dec 31 2015)

                     

                    I should be able to get the turnover ratio for that period.

                     

                    Turnover ratio : no of terminations or no of employees left the company/ no of active employees.

                    suppose I  have Jan1st 100 employees

                      and June 30th 150 employee then I need to add (100+150)/2=250/2 (this is my avg turnover) not sure how to get this in tableau.

                    Turnover Ratio: no of terms/Avg of employee active headcount (this should be dynamic based on the parameter date selection).

                     

                    Please see the below link which i am trying to replicate with my data.

                    https://public.tableau.com/profile/jason.b8401#!/vizhome/TerminationMockDashboard/Turnoverhttps://public.tableau.com/profile/jason.b8401#!/vizhome/TerminationMockDashboard/Turnover

                     

                    Thank you.

                     

                    I really appreciate your help in this.

                     

                    Thank you.

                    1 of 1 people found this helpful
                    • 7. Re: How to calculate  avg employee headcount to get the trunover
                      Stoyko Kostov

                      Hi Deepa,

                       

                      Unfortunately your new column got me confused. I'm not sure how to use it in the calculation.

                       

                      So I used the original book that you uploaded here.

                       

                      This is what I suggest in order to calculate the turnover for a user-defined time period:

                       

                      1. Add 2 parameters: Turnover Start Date and Turnover End Date.

                      2. Define calculated field "Left" as SUM(IF ([Termdate] >= [Turnover Start Date] AND [Termdate] <= [Turnover End Date]) THEN 1 ELSE 0 END). This will count all employees who left at some point in the specified period.

                      3. Define calculated field "Active" as SUM(IF([Hiredate] <= [Turnover End Date] AND ([Termdate] >= [Turnover Start Date] OR ISNULL([Termdate]))) THEN 1 ELSE 0 END). This will count all employees who have been active at any point during the specified period (not necessarily at the end).

                      4. Define Turnover as the quotient of the 2:

                      SUM(IF ([Termdate] >= [Turnover Start Date] AND [Termdate] <= [Turnover End Date]) THEN 1 ELSE 0 END)/

                      SUM(IF([Hiredate] <= [Turnover End Date] AND ([Termdate] >= [Turnover Start Date] OR ISNULL([Termdate]))) THEN 1 ELSE 0 END)

                      5. Format Turnover to show as Percentage.

                       

                      I'm attaching my result. Play with the 2 parameters to see how the data changes. In Sheet 3 I have the overall turnover rate, and in Sheet 5 I have it by BU. Note that "Bux" has not had any active employee during the specified period, so no value is reported (it is null, as it would involve division by 0).

                       

                      Let me know if you need more help with this.

                      1 of 1 people found this helpful
                      • 8. Re: How to calculate  avg employee headcount to get the trunover
                        deepa p

                        Hello Kostov,

                         

                        Thank you very much  for the solution. i will try to replicate the same with my data and let you know how that worked.

                         

                        One quick question when you created Turnover start date and Turnover end date which values you passed to the parameter.is it the Hire date or is it the Term date or you just took some specific date values.Please let me know which values you used to create the date parameters.(Plz find the below attached  image)

                         

                        Regarding the Effective date as per my requirement that is the date which gives me the active employees as of that period.

                         

                        Thanks.

                        1 of 1 people found this helpful
                        • 9. Re: How to calculate  avg employee headcount to get the trunover
                          Stoyko Kostov

                          Hi Deepa,

                           

                          The purpose of parameters is to give control to the user. They are what the user specifies. The results will change as parameters change.

                           

                          The values I put are random - just wanted to demonstrate how they affect the turnover results. You can change them as you wish, and monitor how this changes your results. You can even duplicate the sheet as many times as you wish and define different parameters on each sheet to display turnover for different periods - say, different years.

                          1 of 1 people found this helpful
                          • 10. Re: How to calculate  avg employee headcount to get the trunover
                            deepa p

                            Hello Kostov,

                             

                            Thank you very much for the great solution. Its working as i expected with my data. I really appreciate your time.

                             

                            One more qq solution i needed i want to show the turn over percentage  as a bar chart for the selected period.But what date i have to display on the viz.Now how do I create a date filed based on the parameter?.

                             

                            As reference to the below link i want to display the turnover percentage for respective years.

                             

                            http://https://public.tableau.com/profile/jason.b8401#!/vizhome/TerminationMockDashboard/Turnover

                             

                            Thanks.

                            • 11. Re: How to calculate  avg employee headcount to get the trunover
                              Stoyko Kostov

                              Hi Deepa,

                               

                              The fields that I specified give you the turnover for 2015.

                               

                              To closer mock the dashboard you linked, I duplicated the sheet, changed the fields to point to 2016, and then combined the 2 sheets in a dashboard. Please see attached.

                              2 of 2 people found this helpful
                              • 12. Re: How to calculate  avg employee headcount to get the trunover
                                deepa p

                                Thanks Kostov for the great solution your are genius.