7 Replies Latest reply on Feb 11, 2020 12:13 PM by Jonathan Drummey

    Merge Headcount & Terminations data

    Sandeep Varma

      Hi,

      I am new to Prep and I have Headcount & Terminations data in a separate data-set and need to combine to get Attrition Rate & Terminations Rate.

      And the challenges is the most of the columns are different (Attached 2 data-sets) and user want to do reporting on comparing both Headcount vs Terminations, any ideas how to union these datasets by creating a flag for each data set? And monthly when I run the flow the Output should update with timestamps along with Flag with the excel file?

       

      Needs output like this:

        • 1. Re: Merge Headcount & Terminations data
          Jonathan Drummey

          Hi,

           

          I looked at your data and I can't tell what uniquely identifies an employee in the data so I can't give you an exact answer on how to do this. It's also not clear to me whether the sample employee is being hired multiple times or not, and if so how to identify that in the data. Also the definition of the Numerator and Denominator isn't clear.

           

          In any case the key is that the month you want to show for headcount doesn't actually exist in the data. For example if someone his hired on 1994-01-01 and terminated on 1997-06-12 then they need to show up for all the intervening months, and if nobody was hired or terminated in a given month that month doesn't exist in the data so we need to pad that in.

           

          These instructions assume that a given employee isn't hired multiple times, if that happens then there would need to be a modification (which would depend on how that is identified in the data):

           

          - Create a table of Months up until some point far in the future.

          - Connect to that table of Months and add a filter for Month <= DATETRUNC('month', TODAY()).

          - Connect to the headcount data and create a calculated field for the Hire Month.

          - Use a non-equijoin where Month >= Hire Month. This pads out the data for all months for each employee. If a given employee has multiple

          - Connect to the termination data and create a calculated field for the Termination Month.

          - Join the termination data to the Month+headcount data on the employee id

          - filter the data on ISNULL([Termination Month]) OR [Month] <= [Termination Month]

           

          - Create a Headcount calculated field with a value of 1 (This is assuming that staff count as headcount in their termination month)

          - Create a Total Terms calculated field with a value of IF [Month] = [Termination Month] THEN 1 ELSE 0 END

           

          Normally I'd then output this and then build the view in Tableau Desktop. Month would go as a dimension on Rows then Measure Values on Text with Measure Names on Columns, and the measures SUM(Headcount), SUM(Total Terms), etc. would go onto the Measure Values card.

           

          Jonathan

          • 2. Re: Merge Headcount & Terminations data
            Sandeep Varma

            Person Number uniquely identifies an employee in the data and there is Termination Date as well!

            I need ETL workflow using Tableau PREP with:

            1) Aggregated data of Monthly

            2)New aggregated columns Attrition rate, Number of Employees in Each Age Group & Termination Rate , by Tableau PREP.

            Please find the updated data  and could you help in a ETL workflow using Tableau PREP.

            • 3. Re: Merge Headcount & Terminations data
              Jonathan Drummey

              Hi,

               

              Given what you've posted I can't help you., there are several issues and open questions:

               

              1) The sample data is using RANDBETWEEN() for person number, this creates duplicates in the data, a nonsensical relationship between the headcount data & terminations data, and makes a mess of trying to validate anything because the Person Numbers change from one refresh to the next. Please post data sets that have a unique person number.

              2) You haven't defined how the attrition rate should be computed based on the sample data.

              3) You haven't defined how the termination rate should be computed based on the sample data.

              4) There is no definition of how the Age Groups are supposed to be computed - is this the calendar age of the employee, time of service, or what? If so what is the algorithm for doing this?

              5) Based on the #2 sentence it's not clear which of the measures (attrition rate, number of employees, termination rate) should be grouped by age group.

               

              If you can answer some number of these questions then a volunteer here on the forums could help you out to get you started.

               

              Jonathan

              • 4. Re: Merge Headcount & Terminations data
                Sandeep Varma

                1)Removed duplicates in the data, by  having Unique person number, as well some are common between Headcount & Terminations data.

                 

                 

                2) Attrition rate:= (Number of leaves ÷ number of employees) x 100.

                 

                 

                3)Termination rate: Suppose you lost 33 employees over the last 12 months out of an average workforce of 110. Divide 33 by 110 and multiply by 100 to find the employee turnover rate of 30 percent.

                • 5. Re: Merge Headcount & Terminations data
                  Jonathan Drummey

                  Hi,

                   

                  1)  I still can’t work with this data, the Headcount data has multiple rows for the same person number.

                   

                  3) In your original data the view was by month, but the termination rate is being computed across 12 months, is that correct?

                   

                  Jonathan

                  • 6. Re: Merge Headcount & Terminations data
                    Sandeep Varma

                    Updated the data with  Unique person number and  In my original data the view was by month, but the termination rate is being computed across 12 months!

                     

                     

                    I need to have jump start on this to go in a right direction by having some ETL flow using Tableau PREP.

                    • 7. Re: Merge Headcount & Terminations data
                      Jonathan Drummey

                      Hi,

                       

                      Thanks for the updated data, here's a flow:

                       

                      2020-02-11_15-03-21.png

                       

                      The order of operations was slightly different from what I'd previously described. Also note the several steps in the pink rectangle needed to compute the moving average in Prep since it doesn't have a moving average function at this time.

                       

                      In addition I added a separate Output step right after the monthly counts were computed and then used that in this Tableau Desktop view where the moving averages are computed using table calculations:

                      Screen Shot 2020-02-11 at 3.11.19 PM.png

                       

                      The v2019.4.2 Tableau Prep flow, the Dates Excel spreadsheet used for the padding, and the v2019.4 Tableau workbook are all attached.

                       

                      Jonathan

                      1 of 1 people found this helpful