Calculate Continuos Absent Column

[nuwanhwb87]

Hi,

Im having attached data set with Date selection in my Dashboard. Need to create a column called continuos absent .

Ex:-

(when particular employee absent continuously for 2 days it count as 2 .

If he present to work on next day that count should be zero .

Next day again he absent need to count as 1.

(This need to add continuously when he absent until he present to work)

Please help me on this.

[stevejoyce]

data:

load * inline [
employee, date, absent_fl
1, 9/1/2021, 1
1, 9/2/2021, 0
1, 9/3/2021, 1
2, 9/1/2021, 1
2, 9/2/2021, 1
2, 9/3/2021, 1
3, 9/1/2021, 0
3, 9/2/2021, 1
3, 9/3/2021, 1
]
;

data2:

load *
,if(peek(employee) = employee, if(absent_fl=1, peek(absent_continuous) +1, 0), absent_fl) as absent_continuous
resident data
order by employee, date asc
;

drop table data;

exit script;