3 Replies Latest reply on Sep 24, 2015 8:22 AM by Steve Martin

    Comparing Employee ID - Current Period vs. Last Year End Period.

    Keith Edgar

      Hi.  I really have no idea where to start on this.

       

      What I am trying to achieve is to effectively classify employees as "leavers" or "joiners".  My dataset each month doesn't retain leavers.  So for example an employee in Sept 2014 would be on the dataset (year end), however if they left, on the report for August 2015 they would simply not show on the report.  So I can not utilise "leaver dates" or anything like that.

       

      So I need to effectively create a lookup by EmployeeID which for example does the following

       

      Employee ID    Headcount Qty Sept 2014      Headcount Qty Aug 2015           Category

      A                                        1                                              1                                Current

      B                                        1                                              0                                 Leaver

      C                                        0                                              1                                 Joiner

       

      So a calculated field would determine that there are 1 joiner and 1 leaver. 

       

      I am basically trying to show attrition (within a measure field) since the last year end, but I need it calculated on an employee ID level, so that if I need to drill down I can...

       

      Any ideas where I start?!?

       

      I am a little lost with this!

       

      Thanks

        • 1. Re: Comparing Employee ID - Current Period vs. Last Year End Period.
          Steve Martin

          Hi Keith,

           

          Before we move into the complexities of table calcs to handle this task, can I ask:

           

          • Where & how is you data arranged eg spreadsheet / db etc
            and
          • as s simple calc, could you not deduct the number of rows for each employee - I'm guessing each has one row per month they are with the company, and deduct the number of months your data range is for? eg, uou are in month 9 now and the employee has 8 records so 9-8 = -1, as the value is less than 0 then your employee is a leaver

           

          Steve

          • 2. Re: Comparing Employee ID - Current Period vs. Last Year End Period.
            Keith Edgar

            Hi Steve,

             

            Thanks for getting back to me on this.

             

             

            ·         My data is on a spreadsheet which is linked to Tableau.  A monthly headcount file as such.

             

             

             

            ·         You are correct.  There is essentially only 1 line of data for each employee record per month.  Your simple calc could work for leavers perhaps.  Though I am not sure how I would work this into Tableau?  I need it to do it by employee ID line by line.

             

             

             

            ·         The other element to the simple calc you have suggested, is would it work for joiners though?

             

             

            Thanks for your help Steve, really appreciate the advice !

             

            Keith

            • 3. Re: Comparing Employee ID - Current Period vs. Last Year End Period.
              Steve Martin

              You know, being an speadsheet (excel I presume), you could just build a lookup with match calc direct to Excel and then just employ this in your final report and cut-out Tableau complexity

               

              Steve