2 Replies Latest reply: Oct 16, 2014 8:50 AM by Ola Amphan RSS

    Set Analysis issue regarding start and end dates

    Ola Amphan

      Hi,

       

      I have a problem that I cant get my head around.

       

      I have a table with employees and dates when they started and when they left. Some employees have several start and end dates as they can be temporary hires that come and go. An example is this list:

       

      employee_data:

      LOAD * INLINE [

          emp_id, emp_startdate, emp_enddate

          emp004, 2011-03-01, 2012-02-28

          emp005, 2011-06-01, 2012-12-31

          emp002, 2012-02-01, 2999-12-31

          emp003, 2012-05-01, 2013-06-01

          emp001, 2013-01-01, 2999-12-31

          emp004, 2013-01-01, 2999-12-31

          emp006, 2013-05-01, 2013-12-31

          emp005, 2013-06-01, 2999-12-31

          emp003, 2013-06-02, 2013-11-15

          emp003, 2013-11-16, 2014-05-31

      ];

       

      How would I go about counting how many people have quit for the last time in a specific year, ie. how many emp_ids has an enddate in 2013 and no later startdate than the highest enddate in 2013 for that emp_id, or the opposite, how many people was hired for the first time during 2013.

       

      I have tried to solve this with set analysis but havent been able to get it right. Please see the attached qlikview document for an example.

       

      TIA,

       

      Ola