topic Re: Calculation of difference in time in QlikView

Calculation of difference in time

Tue, 29 Jan 2013 11:39:35 GMT

Hi All,

My requirement is, i have a date and time field in an excel and i have to calculate the difference between various timings. Interval works fine if i select two timings. but i want time difference for all timings.

Eg:

Time:

00:08:49

00:18:48

00:28:46

00:38:44

I Want

Time Diff

00:08:49 Null

00:18:48 00:09:59

00:28:46 00:09:58

00:38:44 00:09:58

I tried different expressions but am not getting this result. I would be very great ful if anyone provide some ideas for this.

PFA Excel.

Thanks in advance.

Re: Calculation of difference in time

Gysbert_Wassenaar — Tue, 29 Jan 2013 11:49:28 GMT

Try this:

Times:

load Date, Localtime,

interval(Date+Localtime - previous(Date) - previous(Localtime),'hh:mm:ss') as TimeDiff

FROM TestTime.xlsx (ooxml, embedded labels, table is Sheet1);

Re: Calculation of difference in time

er_mohit — Tue, 29 Jan 2013 12:04:00 GMT

see the attached file

Re: Calculation of difference in time

Tue, 29 Jan 2013 12:04:38 GMT

I have used the Above() function in a table chart to get the interval (Only(LocalTime) - Above(TheTime)). Attached you have an example with your data.

Re: Calculation of difference in time

Tue, 29 Jan 2013 12:34:19 GMT

Thanks Gysbert, Thanks for your solution.