3 Replies Latest reply on Feb 5, 2019 11:23 AM by Michel Caissie

    Headcount calculation per quarter / Turnover Rate

    Drouin Marie-Eve

      Hey everyone,

       

      I'm super new to Tableau and I'm trying to bring more ''data'' in my HR Team.

      I searched on the forum but didn't seem to find the right answer - don't hesitate to share forum conversation that might help me...

       

      I want to create a Hire & Turnover Dashboard for my director and I'm having a hard time with calculation fields.

      Is there any easy way to calculate the number of employees - per quarter / per year (at that precise moment in time)? I need to know the number of employees at that point in time as well as other company movements: hires, department movements, etc. to calculate the turnover (in %).

       

      I create a fake data set to show you what our data looks like in Excel/Google Spreadsheet. Every employee can have more than one line; we create a new line for each new events: hire, department movements, departures, etc.

       

      I've tried calculating my: arrivals - departures. It works, but only for today's date. Is there any way I can also see the right headcount of my past quarters?

       

      What I wish to create in Tableau:

       

      Capture d’écran, le 2019-01-30 à 10.20.26.png

       

      I've joined and example of my data set (Excel) & my packaged workbook, hope it's everything you need!

       

      Don't hesitate to suggest changes and/or ask questions.

        • 1. Re: Headcount calculation per quarter / Turnover Rate
          Michel Caissie

          Marie-Eve,

           

          There is a couple of complex issues with this kind of problem.

           

          Most of the measures you need can be computed at the row level.

          For example  New Hire could be

          ZN( if CONTAINS([Movement],'New')  then [Arrival] else 0 end )

          So if the Movement value contains the String New  return the Arrival value.  (ZN is ZeroNull  or ifnull then 0,  to display 0 when you have a null instead of an empty string)

           

          EOE could look like

          ZN( if CONTAINS([Movement],'EOE') or CONTAINS([Movement],'End') then -[Depart] end )

          note the minus Depart, since Depart already have negative values.

           

          For the active employee, I guess you want to remove the Internal movement , so the row logic would look like

          if not CONTAINS([Movement], 'Internal') then [Arrival]+[Depart] end

          Which brings to your main question, how to get the value at a specific date.

          Since this number depends on the result of this calculation for the previous rows, you will need a table calculation

          RUNNING_SUM( SUM( if not CONTAINS([Movement], 'Internal') then [Arrival]+[Depart] end) )

          And since this is a table calculation , you need to set the computing.  Right-click the triangle on the green pill and set the following

           

          You can see the result on Test1(mc)  Sheet 1(3).

           

          Now if you want to filter the Date to keep only 2 quarters in the view, you will have a problem  because the runningSum will compute using only those 2 quarters, so all activeEmployees would start at 0 in  2018 Q4.

           

          One way to trick Tableau to compute a table calculation using all the data in the context and not only the data in the view  is to make the date filter a table calculation.

          This have the side effect of changing the order of operation, and all table calculations will compute before the filtering is applied.

          The following table calculation  will simply lookup the date with an index of 0 , so the date itself in the format yyyy Qx

          LOOKUP( MIN( DATENAME('year', [First/last Day] ) + ' Q' + DATENAME('quarter', [First/last Day] ) ),0 )

          You can see the result on  Sheet1(4).

           

          But you have another problem. You don't see all Dept for Q4.  As you can see on the bottom left you have  15 marks.  Each mark represent a number of rows, and Tableau compute each calculation for each mark. But since you don't have any data for  Admin in Q4 , you don't have any mark for that.

          If you look in the  Excel attached, it's a copy of the previous one, but I added a couple of rows in order to have one date for each Quarter for each Dept.

          And if you look on Test1(mc2), I switched the datasource for this file and I now have 24 marks and one row for every Dept in Q4.

          So you will need to do some padding at the source to have a mark for every Quarter for every Dept.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Headcount calculation per quarter / Turnover Rate
            Drouin Marie-Eve

            Hi Michel,

             

            Thank you so much for your help! Really well explained, I've tried some formulas already and it worked great.

            I'll work on my original data set to had my missing marks and I should be good .

             

            Thanks again! My managers will be really happy to have those data!

            • 3. Re: Headcount calculation per quarter / Turnover Rate
              Michel Caissie

              Glad I could help.

              Bonne journée !