Sequence calculated column

Hi Anonymous 

Yes of course!

The data is about employees being absent from work. I have a measure which calculates the frequency of the employee being absent. That measure is based on the column IndexSequenceGroup. But there should be made a small change. The column ‘Verschil’ counts the days between the ‘Start’ (the first day an employee is absent) and the previous row ‘Eind’ (the last day of the previous time the employee was absent). The condition is when ‘Verschil’ < 28, it is count that the employee is never fully returned. So that’s why the number for Employee 465457 with ID 4 should not be 1005, but 997. Because ‘Verschil’ is 3, hence less than 28. (And ‘IndexSequence’ is based on that column: it returns a 0 when ‘Verschil’ is blank or <28, otherwise a 1).

So for employee 508239, the frequency measure should give 2, where it no returns 3. Because the third time, on 25-01-2025, the employee is absent again, within 28 days. It should not return 1000, but 998. And all the following rows are within 28 days, so the number should stay the same.

Hopefully this explains enough context. Feel free to make a completely different formula. The output for example of 1002, 998, etc. is not relevant. It’s more about the sequence.