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 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. 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?