topic Re: calculating time differences in QlikView

calculating time differences

Anonymous — Fri, 17 Apr 2015 20:44:45 GMT

I need to calculate the time differences between the dispensing of any two subsequent prescriptions. The following table illustrates the situation:

ScriptNumber	DateTImeDispensed
161797	30/01/15 18:29
161798	30/01/15 18:45
161798	30/01/15 18:45
161798	30/01/15 18:45
161799	30/01/15 18:49
161800	31/01/15 08:18
161800	31/01/15 08:18
161801	31/01/15 08:39
165073	27/03/15 07:44
161802	31/01/15 09:14
161803	31/01/15 09:38
161804	31/01/15 09:41
161804	31/01/15 09:41
161804	31/01/15 09:41
161805	31/01/15 09:48
161806	31/01/15 09:55
161806	31/01/15 09:55
161806	31/01/15 09:55
161807	31/01/15 10:03
161808	31/01/15 10:19
161809	31/01/15 10:21
161810	31/01/15 10:22
161810	31/01/15 10:22
161811	31/01/15 10:28
161811	31/01/15 10:28

The ScriptNumber is a unique identifier of a prescription. How do i calculate the time difference between any two prescriptions?

Re: calculating time differences

maxgro — Fri, 17 Apr 2015 21:24:20 GMT

ab:

LOAD ScriptNumber,

DateTImeDispensed

FROM

[https://community.qlik.com/thread/160619]

(html, codepage is 1252, embedded labels, table is @1);

tmp:

NoConcatenate load distinct

ScriptNumber, DateTImeDispensed

Resident ab;

ac:

load *,

time(timestamp#(DateTImeDispensed,'DD/MM/YYY hh:mm')- Timestamp#(Peek(DateTImeDispensed),'DD/MM/YYY hh:mm')) as Delta

Resident tmp

order by ScriptNumber;

DROP Table tmp, ab;

Re: calculating time differences

Anonymous — Fri, 17 Apr 2015 22:17:00 GMT

Many thanks Massimo. I am getting the following error message:

Re: calculating time differences

maxgro — Fri, 17 Apr 2015 22:40:48 GMT

my .qvw run without error (PFA)

maybe you have to change the "resident ab" if your table has a different name

Re: calculating time differences

petter — Fri, 17 Apr 2015 22:43:39 GMT

An alternative is to keep your data as it is in your data model and then just use a Straight Table and do the calculation there like this - see also attached QVW:

Re: calculating time differences

Anonymous — Fri, 17 Apr 2015 23:30:26 GMT

Many thanks Petter for the alternative. My results are as follows:

i get blanks in the columns. Is it because of the datetime format? How can this be resolved?

Re: calculating time differences

Anonymous — Fri, 17 Apr 2015 23:35:33 GMT

Many thanks, ran without error but get the following results:

What could be the problem?

Re: calculating time differences

petter — Sat, 18 Apr 2015 06:16:44 GMT

Could be - is it possible for you to upload a sample of your appllication or share a spreadsheet with a bit of the data?

Re: calculating time differences

petter — Sat, 18 Apr 2015 06:22:26 GMT

Try changing the expression to:

Dispensed-Above(Dispensed)

Re: calculating time differences

maxgro — Sat, 18 Apr 2015 07:47:57 GMT

in your first post same script number has same date

in the second same script number has different date and the datetime format changed

better if you can add an extract of your qlikview .qvw

if you want to do the diff in script, use (adapt) my solution

if you want in chart, Petter has already answered (above function), you just have to pay attention to date time format

Re: calculating time differences

Anonymous — Sat, 18 Apr 2015 11:47:58 GMT

Many thanks Petter, changing expression worked. How can i show the average time difference?

Regards

Re: calculating time differences

Anonymous — Sat, 18 Apr 2015 11:51:10 GMT

Many thanks Massimo, comments noted.

Regards

Re: calculating time differences

pratap6699 — Sat, 18 Apr 2015 12:41:40 GMT

show me the all expressions dispences,days also

Re: calculating time differences

petter — Sat, 18 Apr 2015 13:01:51 GMT

You can add a measure with the expression:

Avg(DateTimeDispensed) and label it AvgDisp

Then one more expression like this:

AvgDisp-Above(AvgDisp)

If you want it compared to the average of all then use:

Avg(TOTAL DateTimeDispensed)