0 Replies Latest reply: Mar 8, 2018 4:34 PM by SAIKAT BOSE RSS

    Looping through previous rows and calculating difference in dates.

    SAIKAT BOSE

      I have to calculate the employee continuity and show length of service.

      An employee can have multiple entry and leave dates.

      I have to iterate through all the entries and make sure there is no more than 4 weeks gap between the last employment end date and the next employment start date.

       

       

      Ex. of data is like:

      employee      start date      end date

      EMployee 1     1/1/2017 31/3/2017

      EMployee 1      1/5/2017 28/6/2017

      Employee 1      3/7/2017 31/12/9999

      Employee 2     4/8/2017     31/12/9999

       

      His earliest start date based on conitnuity would be 1/5/2017 and not 1/1/2017 and

      so if I am looking at the data in dec2017 his length os service would be 31/12/2017-1/5/2017 = 8 months

       

       

      Can this be done in qlik sense itself or do I need to calculate in my database based on the logic and bring the earliest start date for any employee into qlik.

      Any sample script would be very helpful.