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.

[#2]

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

[Anonymous]

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

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

[#4]

I test

Order by UniqueComb ,Date asc

but it's not working :S

[Anonymous · Accepted solution]

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.

[#6]

Thanks you very much Dennis,

it's now working