topic Re: Timeline time calculation in QlikView

Timeline time calculation

0li5a3a — Wed, 25 Nov 2020 16:16:04 GMT

Hi there,

Please find my filles attached.

I need an advice regadin how to create a new columns which would do a caulculation like this scenario:

I have a ticket which contian more timelines and I want to caulculate the time between the created and closed end display this in days.

The problem which I have I need to exclude the time when the ticket is in status pending becasue in this status we need to stop the clock.

New, Pending, Work In Progress, Cleared, Closed this is the live sycle for a ticket and the tickets can go any time from Work In Progress & Cleared back in Pending.

What I need to do to display that my ticket had been open for 1day2h...?

Regards,

Re: Timeline time calculation

sunny_talwar — Mon, 04 Jun 2018 14:01:51 GMT

There are two dates create and modified? Duration of create or modified date to find the open time?

Re: Timeline time calculation

0li5a3a — Mon, 04 Jun 2018 14:18:59 GMT

Yes there are two dates create and modified.

I need to know the numbers of Days/Hours/min between the open and close but to exclude all the pending intervals.

thanks

Re: Timeline time calculation

0li5a3a — Mon, 04 Jun 2018 14:23:00 GMT

I need to use the Create Date to do the calcualtion.

Re: Timeline time calculation

sunny_talwar — Mon, 04 Jun 2018 17:07:25 GMT

Try like this

Table:

LOAD * INLINE [

TicketID, Create_Date, Modified_Date, Status

1, 27/11/2017 09:19:59, 27/11/2017 09:19:59, Closed

1, 27/11/2017 09:19:48, 27/11/2017 09:19:48, Cleared

1, 27/11/2017 09:17:02, 27/11/2017 09:17:02, Work In Progress

1, 23/11/2017 08:57:54, 23/11/2017 08:57:54, Pending

1, 22/11/2017 08:48:34, 22/11/2017 08:48:34, Pending

1, 21/11/2017 16:18:34, 21/11/2017 16:18:34, Pending

1, 20/11/2017 13:50:21, 20/11/2017 13:50:21, Work In Progress

1, 20/11/2017 11:33:04, 20/11/2017 11:33:04, Pending

];

FinalTable:

LOAD *,

Interval(If(TicketID = Previous(TicketID), Peek('Create_Date') - Create_Date, 0)) as Duration

Resident Table

Order By TicketID, Create_Date desc;

DROP Table Table;

and then like this

=Interval(Sum({<Status -= {'Pending'}>}Duration), 'D hh:mm:ss')

Re: Timeline time calculation

0li5a3a — Tue, 05 Jun 2018 07:02:40 GMT

Thank you, I would try to implement this now.

Re: Timeline time calculation

0li5a3a — Tue, 05 Jun 2018 10:03:47 GMT

All works fine, thanks.