Set Analysis issue regarding start and end dates

[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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[Amphan]

Thank you for your fast reply!

Ola