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

[Shahzad_Ahsan]

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.

[lironbaram]

hi

try this expression

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

[Shahzad_Ahsan]

Hi Liron

Thanks for your reply.

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.

[christophebrault]

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:
LOAD
    EmpCode,
    Att_Date,
    Absent
FROM [lib://Attendance/Attendance.xlsx]
(ooxml, embedded labels, table is Sheet1);

FINAL:
LOAD * ,
    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)

[Shahzad_Ahsan]

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.

[christophebrault · Accepted solution]

Ok, then the script is :

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

FINAL:
LOAD * ,
    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;

[Shahzad_Ahsan]

Hi

Thanks

This is what I needed

[Shahzad_Ahsan]

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

[christophebrault]

Hi,

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

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

[Shahzad_Ahsan]

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??