5 Replies Latest reply on Mar 27, 2020 1:09 PM by Soumitra Godbole

    Calculating Running Headcount with Attrition and attrition rates

    joyce.shin

      My goal is to create a dashboard of current headcount, how many new people were hired, how many people left, and the attrition rate — by time (by quarters).

       

      I'm struggling with the calculations with the counts of headcount — how do i create a calculation that calculates a running sum of: (headcount + new hires - attrition) with the hire and termination dates.

       

      I'm also struggling with calculating attrition rates for any selected (filtered) time period. For example, I would love to create a line graph of the attrition rates for Q1 2019 - Q4 2019.

       

      Attrition rate calculation: (# people attrited) / (total number of people in time period, current + new hires)

       

      I've attached a sample dataset and workbook!

        • 1. Re: Calculating Running Headcount with Attrition and attrition rates
          joyce.shin

          Hi Soumitra,

           

          Thanks so much for your answer!

           

          I'm sorry, I should have clarified a little more. I have two datasets: one with current headcount of active employees "HC" and another with attritted employees "Attrition". The HC dataset only has one date column "Hire Date" while the "Attrition" dataset has two date columns: Hire Date and End Date.

           

          The first problem I'm having, is combining these two datasets into one. I've currently done an outer join to include all the data in both datasets. But this does not create a field that combines both datasets? How do I create a column /field that combines both datasets?

           

          From your previous answer, how would I need to combine both of them to another single date column? Thank you!

           

          I've attached an updated dataset (with corrected Hire/Term dates, so that the term date occurs after the hire date ).

           

          Thank you so much in advance!!

          1 of 1 people found this helpful
          • 2. Re: Calculating Running Headcount with Attrition and attrition rates
            Soumitra Godbole

            Hi Joyce,

             

              Thank you for your patience. Finally here is your solution and I have attached the

            twbx file along with the excel file that you sent me earlier (I just renamed the 2 sheets

            and added the Date sheet). This method of creating missing dates by doing a join with

            a date column is called "Date Scaffolding" and is a very useful technique.

             

              It was really simple and all we needed to do was first union the data for the 2 tabs HC

            & Attit (keep in mind that the Termination Date will be NULL for employees in HC who have

            not yet left). Once we have this union, then we do a Cartesian Product Join ( m x n ) as

            shown below

             

            Data Join.png

             

            Now we are ready for the analysis in Tableau. Unlike the earlier instance, it is better to do this

            cross product join without having to connect the Hire & Termination Dates to the Date field. We

            will do that when we create a Date Filter (Calculated field) in the next few steps.

             

            Now we create the following 3 fields:

            Formula 1.png

             

            Formula 2.png

                The reason for this is because in Tableau we cannot subtract or compare a null with a date or

                number. I was thinking of using today's date (or the Today() function) but decided to take

                1st Feb instead (you can select your own date as long as it is much later than the last termination

                 date in your datsource)

             

             

            Formula 3.png

                This calculated field is actually the heart of the problem and is to be used as a filter and we exclude

              the Null values.

             

              Finally here is what we come up with. I have set it for Year Quarter but you can also change it to Year

              Month. In this instance I have kept the granularity as Month so I would not have to add too many rows

              in the 3rd tab in the excel workbook. The only issue arises for people who join  towards the end of the

              month and so I added  an extra column in the 3rd tab in excel which is called "Last Day of the Month"

              which has not yet been used.

             

            Visual.png

            This takes care of the first and major part of your problem and do let me know if this works. The rest

            is easy and will follow. Please go through this and let me know the other details you would like and I will

            complete it (The battle is almost won). Hope this was helpful and answered most of your question and

            also the employee names were funny (in my earlier post). Best Wishes !

             

            Sincerely,
            Soumitra

            1 of 1 people found this helpful
            • 3. Re: Calculating Running Headcount with Attrition and attrition rates
              joyce.shin

              This has been SO MUCH HELP already!!! Cannot express my gratitude.

               

              I'll explore more with this and see what I can do

               

              Thanks so much!

              1 of 1 people found this helpful
              • 4. Re: Calculating Running Headcount with Attrition and attrition rates
                Soumitra Godbole

                Hi Joyce,

                 

                  Guess  what ? Here is a much better solution than my previous (evolution). The best part is that

                you can do away with the crazy Date Filter field by incorporating in your requested formulas for HC,

                New Hires & Terminated. I replaced the earlier visual with something of greater value.

                 

                  Also note what my earlier visual displayed was the cumulative HC. Now in addition to cumulative for

                the 3 measures (HC, New Hires & Terminated), by using Table Calculations (with the LOOKUP function

                you can create the difference from previous and get the following by period (Month, Quarter).

                 

                  Also here is what the new Crosstab looks like along with the updated formulas below. Now it should

                be easy for you to calculate the Attrition Rate.

                 

                New Visual.png

                 

                Formulas.png

                Lastly for your reference here is the updated twbx file. Do let me know if you

                need any more calculations. Also in case the above information was helpful, then

                please mark the appropriate solution/s as helpful. This will help me to formulate

                my solution the next time. Thanks !


                Sincerely,
                Soumitra

                1 of 1 people found this helpful
                • 5. Re: Calculating Running Headcount with Attrition and attrition rates
                  Soumitra Godbole

                  Hi Joyce,

                   

                      I know the thread is officially closed but I was checking to see if you saw my most recent

                  post that actually gets you the fields you were looking for to get "Attrition rate". Do let me

                  know in case you have any questions. Thanks !


                  Sincerely,
                  Soumitra