Looping through previous rows and calculating difference in dates.
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.
