Counting of people who missed more than 4 consecutive days

[Anonymous]

Hi guys,

I have a report of staff absenteeism and a business rule that was given to me was that for the analysis of absenteeism do not consider the people who missed more than 4 consecutive days.

I have a report of staff absenteeism and a business rule that was given to me was that for the analysis of absenteeism do not consider the people who missed more than 4 consecutive days.

For example if a person lacked from day 1 until January 6, then for my calculation:

Total absentee hours - (total number of people missing more than 4 consecutive days) * 8

/ (Total active people - (total number of people missing more than 4 consecutive days) * 8

Said person will not be considered from day 5 until the date he is absent, for the others if he is considered as long as he does not miss more than 4 consecutive days, otherwise he will not be considered again.

To solve this problem, I was thinking of creating a field in the script called consecutive, which will tell me how many times the person has been missing consecutively or consecutively. But I do not know what formula could be used for that.

All this I would like to show in a summary table graph.

I ask for your help to spend a happy new year

Thank you so much,

Regards.

[marcus_sommer]

You could use within a sorted resident-load interrecord-functions like Peek() or Previous() ? for checking the previous records and setting a flag-field, maybe with something like this:

load

     *,

     if(rangesum(Date * 3, -peek(Date, -3), -peek(Date, -2), -peek(Date, -1), 6) = 0 and

        rangesum(PersonID * 3, -peek(Date, -3), -peek(Date, -2), -peek(Date, -1)) = 0, 1, 0) as Flag

resident Source order by PersonID, Date;

Important here is the proper sorting. You might need to adjust the conditions maybe to some more if-loops but if you could use numeric and consecutive data for the checks you could just use simple calculations to get true or false.

- Marcus