Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ahsanshahzad
Contributor II

Count Employees who are continuously absent for 10 days or more

Hi Qlikers

I need to count employees who are continuously absent for 10 or more days. Not included in weekends.

The weekend days are Friday and Saturday

Se the sample data, this should count as 1.

  

    

EmpCodeEmpNameDateAbsent
101A05/01/2018Y
101A05/02/2018Y
101A05/03/2018Y
101A05/04/2018N
101A05/05/2018N
101A05/06/2018Y
101A05/07/2018Y
101A05/08/2018Y
101A05/09/2018Y
101A05/10/2018Y
101A05/11/2018N
101A05/12/2018N
101A05/13/2018Y
101A05/14/2018Y
101A05/15/2018Y
3 Replies
isingh30
Contributor III

Re: Count Employees who are continuously absent for 10 days or more

May be this -

Expression - count(if(Absent = 'Y',if(Weekday <> 'Fri','Sat',EmpName)))

Re: Count Employees who are continuously absent for 10 days or more

Hi,

maybe one solution could be:

QlikCommunity_Thread_302864_Pic1.JPG

tabSampleData:

LOAD 100+RecNo() as EmpCode,

    'Name'&RecNo() as EmpName,

    Date(MakeDate(2017)+IterNo()-1) as Date,

    If(Rand()>=(1-If(Peek(Absent)='Y' and 100+RecNo()=Peek(EmpCode),0.6,0.03)),'Y','N') as Absent

AutoGenerate 100

While MakeDate(2017)+IterNo()-1<=Today();

tabResult:

LOAD *,

    If(Absent='Y',If(EmpCode=Previous(EmpCode) and Previous(Absent)='Y',Peek(AbsenceStart),Date)) as AbsenceStart

Resident tabSampleData

Where not Match(WeekDay(Date),'Fri','Sat')

Order By EmpCode, Date;

Left Join (tabResult)

LOAD EmpCode,

    AbsenceStart,

    Date(Max(Date)) as AbsenceEnd,

    Count(Date) as AbsenceDuration,

    AutoNumber(AbsenceStart,EmpCode) as EmpAbsenceID,

    AutoNumber(Hash128(AbsenceStart,EmpCode),'EmpAbsenceID') as AbsenceID  

Resident tabResult

Where AbsenceStart

Group By EmpCode, AbsenceStart;

DROP Table tabSampleData;

hope this helps

regards

Marco

ahsanshahzad
Contributor II

Re: Count Employees who are continuously absent for 10 days or more

Hi Marco

Thanks for your reply

Your answer is almost correct. But there is one problem.

For Some of the employees the 'AbsenseStart' and 'AbsentEnd' is not showing correct.

Please find the attached qvf and excel data. Please see the snapshot for the problem.