topic Re: Sum difference with previous values in QlikView

Sum difference with previous values

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

Hi,

Can you please help me find a solution to this problem?

I have this set of data:

OPERATOR, DATE

Andrew	01-DEC-17 10:40:17	00:01:19
Andrew	01-DEC-17 10:41:36	00:05:45
Andrew	01-DEC-17 10:47:21	00:00:41
Andrew	01-DEC-17 10:48:02	00:03:03
Nicole	01-DEC-17 10:51:05	00:00:05
Nicole	01-DEC-17 10:51:10	00:00:05
Andrew	02-DEC-17 10:51:15	00:02:55
Andrew	02-DEC-17 10:54:10	00:01:30
Sophie	02-DEC-17 10:55:40	00:01:41
Sophie	02-DEC-17 10:57:21	00:01:33

For each operator i would like to get the differnce between every DATE with the previous one and them sum all of these times in order to get the final lead time; for example:

Andrew, 01-DEC

1st activity 10:41:36 - 10:40:17 --> 00:01:19

2nd activity 10:47:21 - 10:41:36 --> 00:05:45

3rd activity 10:48:02 - 10:47:21 --> 00:00:41

The total amount of time is the sum of the results above

I need to make this in backend.

Thank you in advance

Re: Sum difference with previous values

nicu1996 — Mon, 12 Feb 2018 16:32:33 GMT

I think that the Interval() function is what you need: Interval - script and chart function ‒ Qlik Sense

Example:

Load

Interval(end_date - start_date, 'hh:mm:ss') AS Result

;

Re: Sum difference with previous values

Anonymous — Tue, 13 Feb 2018 08:21:29 GMT

Hi,

i've tried with Interval but it's not working; how do I get the end date in my case? I have to get the previous one for each date.

Thanks