Count Employees who are continuously absent for 10 days or more

[Shahzad_Ahsan]

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.

EmpCode	EmpName	Date	Absent
101	A	05/01/2018	Y
101	A	05/02/2018	Y
101	A	05/03/2018	Y
101	A	05/04/2018	N
101	A	05/05/2018	N
101	A	05/06/2018	Y
101	A	05/07/2018	Y
101	A	05/08/2018	Y
101	A	05/09/2018	Y
101	A	05/10/2018	Y
101	A	05/11/2018	N
101	A	05/12/2018	N
101	A	05/13/2018	Y
101	A	05/14/2018	Y
101	A	05/15/2018	Y

[isingh30]

May be this -

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

[MarcoWedel]

Hi,

maybe one solution could be:

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

[Shahzad_Ahsan]

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.