topic Re: Calculation of time difference in QlikView

Calculation of time difference

balrammahato204 — Fri, 20 Nov 2020 18:02:30 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.

Hers 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

swuehl — Sat, 27 Jun 2015 08:25:03 GMT

Not sure if I fully understood your condition. If there is a Pending Status per ID before a In Progress Status, you want the interval not being calculated.

INPUT:

LOAD * INLINE

[

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

ID002,3/20/2015 6:43:25 AM,Assigned

ID002,3/20/2015 6:52:13 AM,Pending

ID002,3/23/2015 2:42:26 AM,In Progress

ID002,3/23/2015 2:42:29 AM,In Progress

ID002,3/24/2015 2:15:23 AM,Pending

ID002,3/27/2015 9:15:23 AM,Pending

ID002,3/28/2015 9:15:23 AM,In Progress

];

ID001 will be calculated, ID002, not.

You can check this condition within a script calculation, grouping your data per ID NO and aggregating the Date values:

INTERVALS:

LOAD *, if(IsNull(Interval),'Omitted') as Flag;

LOAD [ID NO],

Interval(if(Min(if(Status='Pending', Date)) > Min(if(Status = 'In Progress',Date)), Max(if(Status='In Progress', Date)) - Min(if(Status = 'Assigned',Date)) )) as Interval

RESIDENT INPUT

GROUP BY [ID NO];

edit: And please try to avoid multiple postings with the same request. It's hard to follow what's been discussed in the other threads.

Re: Calculation of time difference

balrammahato204 — Sat, 27 Jun 2015 10:47:13 GMT

thanks swuehl for your solution.

But in this calculation i also want to include some more condition-

.Exclude sat sun time and also exclude the holiday

.work hour calculation in between 08:00 AM to 6:00 PM.

How can i put your given logic into networkdays calculation.

Re: Calculation of time difference

swuehl — Sat, 27 Jun 2015 10:57:18 GMT

You can do it like I described here:

Calculate hours between two Date/Time strings

Sample attached.

Re: Calculation of time difference

balrammahato204 — Wed, 01 Jul 2015 14:01:09 GMT

hi swuehl

As per your solution pending time is not excluding from the workingintervel. so please could you look it once. for the calculation i have just taken the simple values in ID001 and trace your script. Then i found

TotalIntervel = 07:00:00

workingintervel=7:00:00

LOAD

* INLINE

[

ID NO,Date,Status
ID001,3/20/2015 8:00:00 AM,Assigned
ID001,3/20/2015 9:00:00 AM,In Progress
ID001,3/20/2015 10:00:00 AM,Pending
ID001,3/20/2015 11:00:00 AM,In Progress
ID001,3/20/2015 12:00:00 PM,In Progress
ID001,3/20/2015 01:00:00 PM,Pending
ID001,3/20/2015 02:00:00 PM,Pending
ID001,3/20/2015 03:00:00 PM,In Progress

ID002,3/20/2015 6:43:25 AM,Assigned
ID002,3/20/2015 6:52:13 AM,Pending
ID002,3/23/2015 2:42:26 AM,In Progress
ID002,3/23/2015 2:42:29 AM,In Progress
ID002,3/24/2015 2:15:23 AM,Pending
ID002,3/27/2015 9:15:23 AM,Pending
ID002,3/28/2015 9:15:23 AM,In Progress
]

Regards

Balram

Re: Calculation of time difference

swuehl — Wed, 01 Jul 2015 14:04:02 GMT

Looks ok to me, ID001 all happens within the same date, same working hour interval 8AM to 6 PM.

So no issue if TotalInterval equals WorkingInterval.

Re: Calculation of time difference

balrammahato204 — Wed, 01 Jul 2015 14:11:56 GMT

actuaaly i want like this

3/20/2015 10:00:00 AM,Pending

3/20/2015 11:00:00 AM,In Progress

ID001,3/20/2015 01:00:00 PM,Pending

ID001,3/20/2015 02:00:00 PM,Pending

ID001,3/20/2015 03:00:00 PM,In Progress

if this situation will come then i want to exclude the time it means from the first condition 1 hour and second condition 2 hour. Total hour is 3 hr. I want also exclude these 3 hour from the WorkingInterval.

Re: Calculation of time difference

pratap6699 — Wed, 01 Jul 2015 16:20:42 GMT

by using Date(maxdatefield)-Date(mindatefield)-->it gives dates differences

interval(hightime)-interval(lowtime)-->it gives time differences with format

Re: Calculation of time difference

swuehl — Wed, 01 Jul 2015 21:04:32 GMT

Please have a look at attached sample file.

Re: Calculation of time difference

balrammahato204 — Fri, 03 Jul 2015 08:37:20 GMT

Swuehl,

now i am getting the perfect output. Now i have required one more help if in this calculation i am adding one more column as a "Priority" and when priority will change previous calculation will be zero and new calculation will start from the last priority change timestamp.

LOAD
* INLINE
[

ID NO,Date,Status, Priority
ID001,3/20/2015 8:00:00 AM,Assigned,P1
ID001,3/20/2015 9:00:00 AM,In Progress,P1
ID001,3/20/2015 10:00:00 AM,Pending,P1,
ID001,3/20/2015 11:00:00 AM,In Progress,P2
ID001,3/20/2015 12:00:00 PM,In Progress,P2
ID001,3/20/2015 01:00:00 PM,Pending,P2
ID001,3/20/2015 02:00:00 PM,Pending,P2
ID001,3/20/2015 03:00:00 PM,In Progress,P3
ID001,3/20/2015 04:00:00 PM,In Progress,P3
ID001,3/20/2015 05:00:00 PM,In Pending,P3
ID001,3/20/2015 06:00:00 PM,In Progress,P3

ID002,3/20/2015 6:43:25 AM,Assigned
ID002,3/20/2015 6:52:13 AM,Pending
ID002,3/23/2015 2:42:26 AM,In Progress
ID002,3/23/2015 2:42:29 AM,In Progress
ID002,3/24/2015 2:15:23 AM,Pending
ID002,3/27/2015 9:15:23 AM,Pending
ID002,3/28/2015 9:15:23 AM,In Progress
]

In this example calculation will start from starting time of P3 and End time of P3 and

It means my total ''workinginterval 2Hour'' for ID001.

Regards

Balram

Re: Calculation of time difference

balrammahato204 — Tue, 07 Jul 2015 05:01:00 GMT

Hi Swuehl,

At first i want to say thank you very much for your solution. My last query for this calculation. I have already post in the previous reply.So could you please check once.

Regards

Balram