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: 
0li5a3a
Creator III
Creator III

Timeline time calculation

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,

C

1 Solution

Accepted Solutions
sunny_talwar

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')


Capture.PNG

View solution in original post

6 Replies
sunny_talwar

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

0li5a3a
Creator III
Creator III
Author

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

0li5a3a
Creator III
Creator III
Author

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

sunny_talwar

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')


Capture.PNG

0li5a3a
Creator III
Creator III
Author

Thank you, I would try to implement this now.

0li5a3a
Creator III
Creator III
Author

All works fine, thanks.