Calculating Date Difference Between Rows

[proctors]

We are trying to work out calculations to tell us the difference between the first time a patient was called for a referral and the first visit they came to the clinic (=Time to Be Seen).

My data looks like the following:

ID	Encounter Type	Reason for Visit	Date
9999	OFFICE VISIT	Visit	2/20/2017
9999	OFFICE VISIT	Visit	12/16/2016
9999	OFFICE VISIT	Visit	5/11/2017
9999	TELEPHONE	Call	11/8/2016
9999	TELEPHONE	Call	10/31/2016

The end Result would be

ID

Date of 1st Call

Date of 1st Visit

Difference Between Dates

[Anil_Babu_Samineni]

Can u explain the result set?

[ahaahaaha]

Hi,

Dimension: ID

Expression Date of 1st Call:

Min ({<[Encounter Type]={'TELEPHONE'}>}Date)

Date of 1st Visit:

Min ({<[Encounter Type]={'OFFICE VISIT'}>}Date)

Difference Between Dates:

Min ({<[Encounter Type]={'OFFICE VISIT'}>}Date)-Min ({<[Encounter Type]={'TELEPHONE'}>}Date)

Result (I added another ID)

Look attached file.

Ragards,

Andrey

[proctors]

This works pretty well!  However, when I looked at other IDs, we saw that some had the following. In this scenario, the min Office visit comes before the telephone call. We want to look forward, someone calls, then has a visit, thus ignoring visits before.

ID	Encounter Type	Reason for Visit	Date
9999	OFFICE VISIT	Visit	2/20/2017
9999	OFFICE VISIT	Visit	12/16/2016
9999	OFFICE VISIT	Visit	5/11/2017
9999	TELEPHONE	Call	11/8/2016
9999	TELEPHONE	Call	10/31/2016
9999	OFFICE VISIT	Visit	4/2/2016

How can I adjust the expression so that it only looks at the first Office Visit after the 1st Telephone call?

Expected Result

ID	Date of 1st Call	Date of 1st Visit (after 1st call)	Difference Between Dates
9999	10/31/2016	12/16/2016

[vvvvvvizard]

Try aggr ahaahaaha reply

Min(Agrr( Min ({<[Encounter Type]={'TELEPHONE'}>}Date), ID))

[proctors]

I'm sorry, what expression is this replacing above? Seems to be returning the ID and not the corrected date difference.