Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
balrammahato204
Creator
Creator

Calculation of time difference

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

4 Replies
Not applicable

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

What if you have two <Pending> continuously ?

balrammahato204
Creator
Creator
Author

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

maxgro
MVP
MVP

1.png


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;

swuehl
MVP
MVP

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.