Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Timeline time calculation

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
Highlighted

Re: Timeline time calculation

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

Highlighted
Creator III
Creator III

Re: Timeline time calculation

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

Highlighted
Creator III
Creator III

Re: Timeline time calculation

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

Highlighted

Re: Timeline time calculation

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

Highlighted
Creator III
Creator III

Re: Timeline time calculation

Thank you, I would try to implement this now.

Highlighted
Creator III
Creator III

Re: Timeline time calculation

All works fine, thanks.