How many return in a sliding window

[#1]

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.

[Oleg_Troyansky]

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

[#3]

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

[#4]

Please help me :S

[#5]

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

[Anonymous]

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.

[#7]

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.

[Anonymous]

Please take a look the attached file.

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

[#9]

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.

[Anonymous]

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

?