count between events

Mon, 06 May 2013 16:23:44 GMT

Hi,

Here is sample data:

PatID Death Date

1 1/1/10

3 2/14/10

8 11/1/12

10 4/17/13

PatID Surgery Date

2 1/4/10

4 2/5/10

5 3/6/10

6 4/8/11

7 9/1/12

9 4/1/13

We are trying to see how many patients have surgery between each death. So what I need to do is Count ( # of surgeries) within a date range from death #1 to death #2, and so on.

so between 1/1/10-2/14/10, there was 1 surgery

between 2/14/10- 11/1/12 there were 4 surgeries

etc.

Any help would be much appreicatted. This seems rather complex!

Re: count between events

swuehl — Mon, 06 May 2013 17:29:47 GMT

If a script based solution is ok, you can try it like this:

SET DateFormat = 'M/D/YY';

INPUT:

LOAD PatID, 'Death' as Type, [Death Date] as Date INLINE [

PatID, Death Date

1, 1/1/10

3, 2/14/10

8, 11/1/12

10, 4/17/13

] WHERE Len(Trim([Death Date]));

LOAD PatID, 'Surgery' as Type, [Surgery Date] as Date INLINE [

PatID, Surgery Date

2, 1/4/10

4, 2/5/10

5, 3/6/10

6, 4/8/11

7, 9/1/12

9, 4/1/13

10,

] WHERE Len(Trim([Surgery Date]));

LOAD Rowno() as Rowno,

PatID, Type, Date,

if(Type= 'Death', AutoNumber(Rowno()), Peek('DeathCount') ) as DeathCount

RESIDENT INPUT ORDER BY Date;

Then create a chart with dimension DeathCount and one expression

=count( if( Type='Surgery',Date))

{or using set analysis

=count( {<Type = {'Surgery'}>} Date)

}

topic Re: count between events in QlikView

count between events

Re: count between events