Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

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.

1 Solution

Accepted Solutions
christophebrault
Specialist
Specialist

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;

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin

View solution in original post

14 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

try this expression

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

Shahzad_Ahsan
Creator III
Creator III
Author

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

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)

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Shahzad_Ahsan
Creator III
Creator III
Author

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

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;

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Shahzad_Ahsan
Creator III
Creator III
Author

Hi

Thanks

This is what I needed

Shahzad_Ahsan
Creator III
Creator III
Author

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

Hi,

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

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
Shahzad_Ahsan
Creator III
Creator III
Author

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