How to get days and records between events?

[#1]

I need to get number of days between two events and the number ofrecords between two events in Qlikview. The orginal data is as follow. I need to know the number of days between surgery infections and the number of records between infections. The summary table is attached to the data table. Is there any way to do it in Qlikview?

Thanks

PatientID	SurgeryDate	Infection
1	10/21/2012	Yes
2	10/22/2012	No
3	10/23/2012	No
4	10/24/2012	No
5	10/25/2012	No
6	10/26/2012	No
7	10/27/2012	No
8	10/28/2012	No
9	10/29/2012	No
10	10/30/2012	No
11	10/31/2012	No
12	11/1/2012	No
13	11/2/2012	Yes
14	11/3/2012	No
15	11/4/2012	No
16	11/5/2012	No
17	11/6/2012	No
18	11/7/2012	No
19	11/8/2012	No
20	11/9/2012	No
21	11/10/2012	No
22	11/11/2012	No
23	11/12/2012	Yes
24	11/13/2012	No
25	11/14/2012	No
26	11/15/2012	No
27	11/16/2012	No
28	11/17/2012	No
29	11/18/2012	No
30	11/19/2012	No
31	11/20/2012	No
32	11/21/2012	No
33	11/22/2012	No
34	11/23/2012	YES
35	11/24/2012	No
36	11/25/2012	No
37	11/26/2012	No

PatientID	SurgeryDate	Infection	Number of days between surgeries	No. of surgeries between infection
1	10/21/2012	Yes
13	11/2/2012	Yes
23	11/12/2012	Yes
34	11/23/2012	YES

[hic]

One way is

LOAD *,

     if(Infection='Yes',Peek(RecordCounter)+1) as RecordsSinceLastInfection,

     if(Infection='Yes',SurgeryDate - Peek(DateOfLastInfection) + 1) as DaysBetweenInfections;

LOAD *,

     if(Infection='Yes',SurgeryDate,Peek(DateOfLastInfection)) as DateOfLastInfection;

LOAD PatientID,

     Date#(SurgeryDate, 'MM/DD/YYYY') as SurgeryDate,

     Capitalize(Infection) as Infection,

     if(Capitalize(Infection)='Yes',0,Peek(RecordCounter)+1) as RecordCounter

FROM [Infections.xlsx] (ooxml, embedded labels, table is Sheet1);

HIC

[#3]

Is this possible to do the same thing at expression level? I would like o make it dynamic. If users are using filters, the number of days and the number of surgeries will change. By using set analysis, I can get surgery infection cases and use Above function to days between adjacent dates. But I do not know whether I can get numbers of records between adjacent dates.

Thank you very much.

[hic]

If you can solve the date problem using Set Analysis and the Above() function, you should be able to do the same with number of records. Use the RowNo() function to find the record number in the chart.

HIC

[#5]

Thank you for your suggestions. I can get number of days between adjacent dates on which surgery infection occurred, but I cannot get the number of records using RowNo() function. Please see the sample file in the attachment.

[danielact · Accepted solution]

If each surgery gets a new PatientID, and they're always ordered by date, use this attachment. If they're not, that's simple enough to set up in your script, and use the new field instead of PatientID.

[#7]

It works as it should be. Thanks.

I do have two questions about your script.

1. You add all fields into Dimensions and add =if(Infection='Yes',1,null()) as a new dimension. It looks like it can be used to filter the data points on x-axis. Is this correct? I actually tried to use set analysis to limit the data points on x-axis to only the record whose infection is equal to 1. What is the logic here?

2. In two expressions you used, TOTAL key word is used. Why does the TOTAL do here?

3. Is there any way to hide the first data point since it is always 0?

Thanks again

[danielact]

For the first point, I think that set analysis works best when there's a calculation being performed, not just a simple "use only this value". You can combine the calculated dimension with the infection field instead - just switch the last dimension to =if(Infection='Yes',Infection,null()) and you can get rid of the first one (make sure you set it to supress null values!)

The TOTAL keyword is used so that the above function will work properly

I'm not sure about hiding the first row. I'm sure there's a way, I just can't think of it right now.

[#9]

Hello!

How make this work if my PatientID is not sequential?

I need to know a number of days between events, to  know which patients did a same event in  less seven days.