13 Replies Latest reply: Jul 19, 2018 1:33 AM by Shahzad Ahsan

# Get Employee count who are continuously absent for 10 or more days

Hi

I need to count employees who are continuously absent for 10 or more days. Excluding weekends, here weekend days are Fri and Sat

Sample data and qvf is attached. Please update in this qvf .

This sample data contains total 5 employees out of which 2 are absent continuously for 10 or more days. Those are,1001 and 1004. I want measure expression that count these 2 employee.

• ###### Re: Get Employee count who are continuously absent for 10 or more days

hi

try this expression

sum(aggr(if(count({<Absent={"Y"}>}Att_Date)>=10,1,0),EmpCode))

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi Liron

But here is one issue in this expression, this is counting all employee who have more than 10 absent.

I need only those emp who has continuously 10 or more absent.

If you check for empcode 1002, he has 13 absent but if you check his dates, he is not absent continuously. So 1002 should be excluded.

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi,

Your Dataset does'nt contain enought data where Y is repeated 10 times continously.

Below is my solution, check the attached app also. You must edit the data connexion to make it work.

This script increment the field AbsDuration :

TEMP:
EmpCode,
Att_Date,
Absent
FROM [lib://Attendance/Attendance.xlsx]
(ooxml, embedded labels, table is Sheet1);

FINAL:
if(EmpCode=Peek(EmpCode) and Absent='Y',Peek(AbsDuration)+1,0) as AbsDuration

Resident TEMP
order by EmpCode,Att_Date asc;

drop table TEMP;

And then use it in an expression :

Count(distinct {\$<AbsDuration={">=10"}>}EmpCode)

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi Christophe

My data contains enough data for repetition of Y continuously. For that you have to exclude weekends i.e. Fri and Sat.

Weekend data has N and this should not be considered.

For Example if you check for empcode 1001, he is absent from 03-Jun to 18-Jun. If you exclude the weekends, he is absent for 12 continuous days.

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Ok, then the script is :

TEMP:
EmpCode,
Att_Date,num(weekday(Att_Date)) as NoDay,
Absent
FROM [lib://Attendance/Attendance.xlsx]
(ooxml, embedded labels, table is Sheet1);

FINAL:
if(EmpCode=Peek(EmpCode) ,
If(NoDay<5    and Absent='Y',Peek(AbsDuration)+1,
If(NoDay<5    and Absent='N',0, Peek(AbsDuration))),0)
as AbsDuration

Resident TEMP
order by EmpCode,Att_Date asc;

drop table TEMP;

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi

Thanks

This is what I needed

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi Christophe

I need one more additional help here

How to count number of days employee is absent

Previously, there was sample data for one month only, so I used Max(AbsDuration)

When I go live this is not working

Please find new sample data and qvf

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi,

Max(AbsDuration) give you only the longest absence. If you want the number of days, use :

Count(distinct {\$<Absent={'Y'}>}Att_Date)

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi Christophe

I have one more question related to this

I need , if month changes then 'AbsDuration' should reset. It should again start from 1.

How is this possible??

• ###### Re: Get Employee count who are continuously absent for 10 or more days

Hi,

I don't have so much time, but is this script working as desired ?

TEMP:

EmpCode,Month(Att_Date) as Month,

Att_Date,num(weekday(Att_Date)) as NoDay,

Absent

FROM [lib://Attendance/Attendance.xlsx]

(ooxml, embedded labels, table is Sheet1);

FINAL:

if(EmpCode=Peek(EmpCode) ,

If(NoDay<5    and Absent='Y',Peek(AbsDuration)+1,

If(NoDay<5    and (Absent='N' or peek(Month)<>Month),0, Peek(AbsDuration))),0)

as AbsDuration

Resident TEMP

order by EmpCode,Att_Date asc;

drop table TEMP;

• ###### Re: Get Employee count who are continuously absent for 10 or more days

No, This is not working.

• ###### Re: Get Employee count who are continuously absent for 10 or more days

I need something like this

EmpCodeDateAbsenceAbsDuration(Old)AbsDuration(Needed)
100527-May-2018Y11
100528-May-2018Y22
100529-May-2018Y33
100530-May-2018Y44
100531-May-2018Y55
100501-Jun-2018N55
100502-Jun-2018N55
100503-Jun-2018Y61
100504-Jun-2018Y72
100505-Jun-2018Y83
100506-Jun-2018Y94
100507-Jun-2018Y105
• ###### Re: Get Employee count who are continuously absent for 10 or more days

Finally I achieved what I needed

TEMP:

EmpCode,Month(Att_Date) as Month1,

Att_Date,num(weekday(Att_Date)) as NoDay,

Absent

FROM [lib://Attendance/Attendance.xlsx]

(ooxml, embedded labels, table is Sheet1);

FINAL:

if(EmpCode=Peek(EmpCode) ,if(Month1=peek(Month1),

If(NoDay<5 and Absent='Y' ,Peek(AbsDuration)+1,

If(NoDay<5 and Absent='N' ,0, Peek(AbsDuration))),0),0)

as AbsDuration

Resident TEMP

order by EmpCode,Att_Date asc;

drop table TEMP;