Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
There are two dates create and modified? Duration of create or modified date to find the open time?
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
I need to use the Create Date to do the calcualtion.
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')
Thank you, I would try to implement this now.
All works fine, thanks.