Closest Value

[#1]

Hi

I have a 2 dates that I need to compare with each other and get the closest date to a certain date.

IE I have a Patient that was serviced on these days.

2013/01/03

2013/01/10

2013/01/23

2013/02/04

2013/03/27

2013/04/11

2013/04/17

Then I have the same patient that got a letter on these days. Lets call this the Dispatched Date.

2013/05/15

2013/06/12

What I need is the following:

PatientID	Service Date	Closest Letter Date
109886-0	2013/01/03	2013/05/15
109886-0	2013/01/10	2013/05/15
109886-0	2013/01/23	2013/05/15
109886-0	2013/02/04	2013/05/15
109886-0	2013/03/27	2013/05/15
109886-0	2013/04/11	2013/05/15
109886-0	2013/04/17	2013/05/15
109886-0	2013/06/02	2013/06/12

Can you help?

I have done the following:

Calculation: Date(min(aggr(if(fabs([Dispatched Date]-ServiceDate) =

min(fabs([Dispatched Date] - ServiceDate)), [Dispatched Date]), [Dispatched Date])))

Dimention: Service Date and PatientID

Now this will give me the closest value to a "Selected" service date. So i have to select the date each time and then according to that service date the calculation will work. I need to do this without a selection.

I got around the problem using a macro to select each service date and export a QVD, then partial reload all the QVD's back into QlikView. This is not good practice. Especially where I want to use this.

Please can anyone help??

Thanks in advance.

Jandre