topic Re: How many return in a sliding window in QlikView

How many return in a sliding window

Thu, 31 May 2012 14:46:38 GMT

Hello all,

I am facing a problem and i don't have idea...

I am a doctor. I have patients. I want to know how many times my patient returned in a sliding window of 6 months for the same disease:

Patient1 Disease A 01/01/2011

Patient1 Disease A 01/04/2011

Patient1 Disease A 01/09/2011

So, i have for this patient 2 return.

Other example :

Patient2 Disease B 01/01/2011

Patient2 Disease B 01/08/2011

Patient3 Disease A 01/02/2011

Patient3 Disease C 01/03/2011

Patient4 Disease D 01/08/2011

Patient4 Disease D 01/03/2012

Patient1 Disease D 01/05/2012

So, I have : - Patient B = 0 return

- Patient A = 0 return

- Patient D = 1 return

I think I need to use a for with a group by into the script but I can't do this...

Please help me,

Best Regards.

How many return in a sliding window

Oleg_Troyansky — Thu, 31 May 2012 17:35:46 GMT

I think the logic in the script should follow something like this:

1. Determine the First Visit Date for each Patient and Disease (using Group By and min(Date)).

2. Join the First Visit Date back into the detailed table.

3. Reload the data agin with the Group By accumulation and the condition :

WHERE Date <= FirstVisitDate+180 //6 months since the first visit

In this aggregated load, count the number of visits that met the condition and subtract 1 to exclude the first visit itself.

hope it covers your needs,

Oleg

How many return in a sliding window

Fri, 01 Jun 2012 13:20:51 GMT

I do not think it will work.

After the first date, i have to compare the second date with the third date and so forth...

I'm not a expert in Qlikview and maybe i did not undertand your answer...

Thank you for your answer and your help

Re: How many return in a sliding window

Tue, 12 Jun 2012 07:43:42 GMT

Please help me :S

Re: How many return in a sliding window

Tue, 12 Jun 2012 09:02:01 GMT

Hi,

Please find the attached file as a solution to your problem.

I have implemented what Oleg was saying.

I hope this will solve your purpose.

Pratibha

Re: How many return in a sliding window

Anonymous — Tue, 12 Jun 2012 10:39:41 GMT

A little different approche would be to calculate the differnce between the dates in your script like this:

Info:
LOAD *,
          Patient &'-'& Disease as UniqueComb,
          RowNo()                     as           ID;
LOAD * INLINE [
    Patient, Disease, Date
    Patient2, Disease B, 01/01/2011
    Patient2, Disease B, 01/08/2011
    Patient3, Disease A, 01/02/2011
    Patient3, Disease C, 01/03/2011
    Patient4, Disease D, 01/08/2011
    Patient4, Disease D, 01/03/2012
    Patient1, Disease D, 01/05/2012
];
Drop field Patient, Disease from Info;

Test:
Load
          UniqueComb,
          Interval#(Date - Previous(Date) , 'DD/MM/YYYY') as DifferencePreviousDate
Resident Info ;

This will create the field "DifferencePreviousDate" which will give you the number of days between 2 dates of the combination Patient - Desease.

This field you can use in your expression like:

IF (DifferencePreviousDate < '61', Count(ID))

With the field UniqueComb as your dimension.

Good Luck,

PS You have to make sure your data is in the right Date order to use the Previous() function.

Re: How many return in a sliding window

Wed, 13 Jun 2012 09:47:38 GMT

Hi all,

Thank you very much.

The first solution in application revisit of patients.qvw, is not far of the true solution.

This solution test the first date with the others. But it not test the second with the others, the third with the others.

So, for exemple, if i have this :

Patient1 ,Disease A ,01/01/2011

Patient1 ,Disease A ,01/04/2011

Patient1 ,Disease A ,01/08/2011

The application don't count the third record. But 01/08/2011 - 01/04/2011 < 6 month, so it's false.

I want to test all record.

Best Regards.

Re: How many return in a sliding window

Anonymous — Wed, 13 Jun 2012 09:59:03 GMT

Please take a look the attached file.

Let me know if this is what you are looking for.

Re: How many return in a sliding window

Wed, 13 Jun 2012 11:56:16 GMT

Hi Dennis,

Thanks for your answer.

I must look the graph "count last 60 days", really ?

I think it's not working. In your application, the function previous not working very well.

For example, for patient2 and diseaseB, if i change the date for the second visit, i have already 2 in your graph.

Re: How many return in a sliding window

Anonymous — Wed, 13 Jun 2012 12:07:49 GMT

What happens when you add an 'order by' to the resident load?

Like:

Load

UniqueComb,

Interval#(Date - Previous(Date) , 'DD/MM/YYYY') as DifferencePreviousDate

Resident Info

Order by Date asc

Re: How many return in a sliding window

Wed, 13 Jun 2012 12:37:49 GMT

Yeahhhh, now it's working.

But i have a strange problem :

Patient, Disease, Date

Patient2, Disease B, 01/01/2011

Patient2, Disease B, 01/02/2012

Patient3, Disease A, 01/02/2011

Patient3, Disease C, 01/03/2011

Patient4, Disease D, 01/08/2011

Patient4, Disease D, 01/03/2012

Patient1, Disease D, 01/05/2012

Patient1 ,Disease A ,01/01/2011

Patient1 ,Disease A ,01/02/2011

Patient1 ,Disease A ,01/03/2011

For this, i have :

UniqueComb	Count last 60	days
Patient3-Disease	A	1
Patient3-Disease	C	1
Patient2-Disease	B	2

So, for patient1 disease A it's false, but if i change for patient2 and disease B the date :

Patient2, Disease B, 01/01/2011

Patient2, Disease B, 01/08/2012

Patient3, Disease A, 01/02/2011

Patient3, Disease C, 01/03/2011

Patient4, Disease D, 01/08/2011

Patient4, Disease D, 01/03/2012

Patient1, Disease D, 01/05/2012

Patient1 ,Disease A ,01/01/2011

Patient1 ,Disease A ,01/02/2011

Patient1 ,Disease A ,01/03/2011

];

I have :

UniqueComb	Count last 60	days
Patient3-Disease	A	1
Patient3-Disease	C	1
Patient1-Disease	A	3

And it's true for patient 1Disease A

Why ? I hope it's clear for you

Re: How many return in a sliding window

Anonymous — Wed, 13 Jun 2012 13:24:18 GMT

Somehow you have to find a way to group your UniqueComb and sort the dates within these groups.

Before the: Interval#(Date - Previous(Date))

Re: How many return in a sliding window

Thu, 14 Jun 2012 11:31:57 GMT

I test

Order by UniqueComb ,Date asc

but it's not working :S

Re: How many return in a sliding window

Anonymous — Fri, 15 Jun 2012 09:37:04 GMT

Sorry you are right, try this in your script;

Test:

Load ID,

DifferencePreviousDate ,

If (DifferencePreviousDate < '61' , 1 , 0) as Count60;

Load

ID,

IF(UniqueComb =Previous(UniqueComb) , NUM(Interval(Date) - (Previous(Date)) ) , 9999) as DifferencePreviousDate

Resident Info

If you dont do this it will count the difference (in days) between the previous row, even if the Unique Combination is not the same.

Check the attached file and let me know if this works for you.

Good Luck,

Dennis.

Re: How many return in a sliding window

Mon, 02 Jul 2012 07:45:14 GMT

Thanks you very much Dennis,

it's now working