topic Re: Calculation of time difference in QlikView

Calculation of time difference

balrammahato204 — Sat, 27 Jun 2015 06:31:21 GMT

Hello Guys, please help me i got stuck in one problem:-

I want to calculate the Total time difference from Assigned status to last In Progress status.

But there is a condition if there is a any Pending status in the column and then after just there is a first in progress status, time difference should be exclude. Eventually, what would be the final time difference.

Here is a scenario:---

ID NO	Date	Status
ID001	3/20/2015 6:43:25 AM	Assigned
ID001	3/20/2015 6:46:56 AM	In Progress
ID001	3/20/2015 6:52:13 AM	Pending
ID001	3/23/2015 2:42:26 AM	In progress
ID001	3/23/2015 2:42:29 AM	In progress
ID001	3/24/2015 2:15:23 AM	Pending
ID001	3/27/2015 9:15:23 AM	Pending
ID001	3/28/2015 9:15:23 AM	In Progress

Re: Calculation of time difference

Sat, 27 Jun 2015 07:05:14 GMT

So, you want to exclude two lines : <Pending> and the next <Next in Progress> ?

What if you have two <Pending> continuously ?

Re: Calculation of time difference

balrammahato204 — Sat, 27 Jun 2015 07:25:07 GMT

when getting two pending continuously then consider <first pending> status and the <Next in Progress> time

Re: Calculation of time difference

maxgro — Sat, 27 Jun 2015 07:40:28 GMT

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

source:

LOAD [ID NO],

Date as DateOrig,

Timestamp(date#(Date, 'MM/DD/YYYY hh:mm:ss tt')) as Date,

Status

FROM

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

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

table:

load

Date - Peek(Date) as Delta,

if(peek(Status)='Pending' and upper(Status)='IN PROGRESS', 0, 1) as Coeff

Resident source

order by [ID NO], Date;

DROP Table source;

Re: Calculation of time difference

swuehl — Sat, 27 Jun 2015 10:43:27 GMT

I've posted a different solution here:

Calculation of time difference

Not sure if this is what you want, it's hard to follow the same topic in different threads. Please try to avoid X-posting.