New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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;

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

Contributor II

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.

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

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.

Contributor III

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;

Contributor II

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

Hi

Thanks

This is what I needed

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

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)

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