topic Re: Set Analysis: Date Comparison Two Fields in QlikView

Set Analysis: Date Comparison Two Fields

Mon, 06 Aug 2012 09:03:35 GMT

HI All,

I have been searching on the community and have found similar examples but nothing that will quite do what I require.

I am stuck with a set analysis (or If statement) expression using dates from two feilds. What I Require is:

- - To flag all the ‘hospital numbers’ that have a ‘Event One Date’ within 7 days of the ‘Event Two date’. This could be seven days before or seven days after.

- - There is also the added complication that there could be multiple ‘Event One’ or ‘Event Two’ for each hospital number. Where this is the case we would want it to compare any ‘Event One Date +/- 7 days’ to any ‘Event Two Date’.

Where have I got to?

- Currently not very far!!! In the attached example I have just been trying to flag Hospital Numbers where Event one/two dates are equal. I was going to build on the expression from there however I am even having trouble doing this.

- What I am thinking is that for each hospital number we would first need to create a set of all the possible ‘Event One Dates’ we then need to look for the intersection with the ‘Event Two Dates’.

Please see attached an Example file to play aorund with.

Any help would be appreciated and I am really looking forward to increasing my understanding of how this would work.

Thanks

Dan

Re: Set Analysis: Date Comparison Two Fields

swuehl — Mon, 06 Aug 2012 10:04:57 GMT

Your right, your problem in the second table is due to the fact, that the set expression is evaluated once per chart.

Not sure if you can create a set expression for what you want to achieve.

You can probably work out a solution with advanced aggregation (aggr() function and some conditionals).

I followed a different approach, creating an interval match in the load script. Not sure if this fits your needs, but maybe give you an idea on how to solve this.

Regards,

Stefan

Re: Set Analysis: Date Comparison Two Fields

Mon, 06 Aug 2012 11:33:20 GMT

Thanks Stefan. That is a good back up option and could be expanded to work for 'n' fields. Ideally though I would do it using set analysis or IF statement so i can let users input the numbers of days between events.

If you or anyone else has further ideas that would be great.

Thanks

Dan

Re: Set Analysis: Date Comparison Two Fields

swuehl — Mon, 06 Aug 2012 11:44:52 GMT

In your table chart with dimensions Hospital Nr, Event One Date and Event Two Date, just use

=sum(if([Event One Date] <= [Event Two Date]+7 and [Event One Date]>= [Event Two Date]-7,1,0))

You can replace the 7 with a variable and set the variable in a slider / input box.

Re: Set Analysis: Date Comparison Two Fields

Mon, 06 Aug 2012 11:54:25 GMT

Thanks. This is definitly getting closer. The only problem here is it doesnt seem to check for all the possible dates for that hospital number. In the example attached i have extended it to 400 days but this is not picking it up. I think this is because it has the event one and event two dates on different lines in the table. Any ideas on hwo to get around this?

Re: Set Analysis: Date Comparison Two Fields

swuehl — Mon, 06 Aug 2012 11:59:57 GMT

You need to remove the intervalmatch from your load:

//JOIN IntervalMatch ([Event One Date], [Hospital Number]) LOAD [Event Two Start], [Event Two End], [Hospital Number] Resident TAB2;

Re: Set Analysis: Date Comparison Two Fields

mark_casselman — Mon, 24 Jun 2013 22:17:56 GMT

Dan,

Did you finally find a solution to your problem ? I'm very interested as we face a similar problem comparing multiple events in a process and looking for events occuring within a time span of x days.

Thanks,

Mark