Solved: Find dates - Qlik Community

qvqfqlik · ‎2017-04-07

I need to count the number of days consecutively based on the list of dates in the data(irrespective of any holidays) .

For the "counter" Iam getting just one value '1'.

MK_QSL · ‎2017-04-12

1) NewFlag_14DaysCounter is having Value 1 and 0. 1 will give you employee worked >=15 Days consecutively.

2) Select 1 from this list box. This will filter Counter having values >=15 days.

3) This will also filter Employee ID respectively.

4) Select any Employee ID.. i.e. 5666.

5) Table box now will show you data correspond to this Employee. You can see that his emplyee worked between 11/15/2016 to 12/2/2016 (18 days).

6) I have also created FromDate and ToDate which will give you information when the consecutive work days start and finish.

HTH.

Regards,

MK

Temp:

Load

[Employee ID],

DATE,

if(rowno()=1 or [Employee ID] <> previous([Employee ID]) ,1,

if(DATE-previous(DATE)=1,RangeSum(Peek('Counter'),1),1)) as Counter,

if(rowno()=1 or [Employee ID] <> previous([Employee ID]) or (DATE - Previous(DATE))<>1,DATE,PEEK(TEMPDATE)) as TEMPDATE

Resident Kronos

order by [Employee ID],DATE;

Temp2:

Load *, AutoNumber([Employee ID] & TEMPDATE, 'TEMPDATE') as Flag Resident Temp;

Drop Table Temp;

Temp3:

Load

[Employee ID],

Flag,

Max(Counter) as Counter,

Date(Min(DATE)) as FromDate,

Date(Max(DATE)) as ToDate

Resident Temp2

Group By Flag, [Employee ID];

Left Join(Temp3)

Load [Employee ID], Flag, Max(Counter) as MaxCounter Resident Temp3 Group By [Employee ID],Flag;

Left Join(Temp2)

Load *

,If(MaxCounter >= 15, 1,0) as Flag_15DaysCounter Resident Temp3;

Drop Table Temp3;

Temp4:

Load

*

,If(IsNull(Flag_15DaysCounter),Peek('NewFlag_15DaysCounter'), Flag_15DaysCounter) as NewFlag_15DaysCounter

,If(IsNull(MaxCounter),Peek('NewMaxCounter'), MaxCounter) as NewMaxCounter

,If(IsNull(FromDate),Peek('NewFromDate'),FromDate) as NewFromDate

,If(IsNull(ToDate),Peek('NewToDate'),ToDate) as NewToDate

Resident Temp2 Order By [Employee ID], DATE Desc;

Drop Table Temp2;

Drop Fields FromDate, ToDate, TEMPDATE, MaxCounter, Counter, Flag_15DaysCounter, Flag;

Rename Fields NewFromDate to FromDate, NewToDate to ToDate, NewMaxCounter to Counter, Flag_15DaysCounter to Flag_15DaysCounter;

Left Join (Kronos)

Load * Resident Temp4;

Drop Table Temp4;

EXIT SCRIPT;

View solution in original post

qvqfqlik · ‎2017-04-09

Please help with finding consecutive dates

Anonymous · ‎2017-04-09

How about this ?? How to calculate consecutive work days?

qvqfqlik · ‎2017-04-09

I tried it, it wasn't working for me. Can you please help or correct the script I posted

Anonymous · ‎2017-04-09

Hi

Can you attach the data file, that may help to understand the problem better.

Regards

Bhuvan Agarwal

Anil_Babu_Samineni · ‎2017-04-09

Would you share excel data and expected result please? Because first loaded time you used hard coded date. We are not getting it the use

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

qvqfqlik · ‎2017-04-10

I have attached the file to my original post.

I need to find employees who worked for more than 10 consecutive dates.

I don't have the count of no.of consecutive days of work in the data source.

I need to find the count of consecutive dates from the list of dates an employee has worked.

Iam looking for something like this How to calculate consecutive work days?

Anil_Babu_Samineni · ‎2017-04-10

I never worked with it, That is the case Gysbert Wassenaar can help you.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

MK_QSL · ‎2017-04-10

1) This can not be achieved (or say very difficult) to do at front end. We have to achieve the same using Script. For that you have to provide sample (dummy) data.

2) If you can't provide dummy data then you can try How to calculate consecutive work days?.

qvqfqlik · ‎2017-04-10

I have attached sample data and the error to my post. Please help me to correct this.

I need to count the number of days consecutively based on the list of dates in the data(irrespective of any holidays) .

I have written sum() for the sake of writing group by like in the other thread.

Find dates

Related Topics