topic Re: Calculating Date Difference Between Rows in QlikView

Calculating Date Difference Between Rows

proctors — Wed, 25 Nov 2020 16:16:04 GMT

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

Date of 1st Call

Date of 1st Visit

Difference Between Dates

Re: Calculating Date Difference Between Rows

Anil_Babu_Samineni — Fri, 14 Jul 2017 03:07:49 GMT

Can u explain the result set?

Re: Calculating Date Difference Between Rows

ahaahaaha — Fri, 14 Jul 2017 04:11:14 GMT

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

Re: Calculating Date Difference Between Rows

proctors — Fri, 14 Jul 2017 12:51:37 GMT

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

Re: Calculating Date Difference Between Rows

vvvvvvizard — Fri, 14 Jul 2017 13:14:07 GMT

Try aggr ahaahaaha reply

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

Re: Calculating Date Difference Between Rows

proctors — Fri, 14 Jul 2017 14:23:40 GMT

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