Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Shahzad_Ahsan
Contributor II

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
christophebraul
Contributor III

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

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;

13 Replies
lironbaram
Honored Contributor II

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

Shahzad_Ahsan
Contributor II

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

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.

christophebraul
Contributor III

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:
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
Contributor II

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.

christophebraul
Contributor III

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

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
Contributor II

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

Hi

Thanks

This is what I needed

Shahzad_Ahsan
Contributor II

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



christophebraul
Contributor III

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)

Shahzad_Ahsan
Contributor II

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