Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I need to create a age bucket basis the no of days elapsed in treating the ticket, the concern here is a ticket can be reopened multiple times so basis the status i will need to calculate no of days involved on the ticket.
For Eg:
1. Ticket opened and closed once in its entire journey then open date- close date
2. Ticket opened on Jan 1st , closed on 15th, reopened on Feb 5th closed on 10th and reopened on Oct 1 and closed on Oct 20 then no of days would be 15+5+20=40 days
3. Ticket opened on Jan 1st , closed on 15th, reopened on Feb 5th closed on 10th and reopened on Oct 1 and is still in progress then no of days would be 15+5+38(Oct 1 to Nov 8th)=58 days
Please let me know how this can achieved with less impact on performance considering there are millions of tickets, attaching a sample data for reference
Hi, with this script you can calculate how many days has been each instance of 'Open', it counts day until the next closed, or until today.
ProcessedData:
LOAD
TicketId,
Status,
Date,
If(Status='Open'
,If(Peek(TicketId)=TicketId and Peek(Status)='Close'
,Peek(Date)-Date
,Today()-Date)
,0) as DaysOpened
Resident InitialData
Order by TicketId, Date desc;
With this you can do a group by TicketId and sum(DaysOpened) to count how many days each ticket has been in Open state.
Hi, with this script you can calculate how many days has been each instance of 'Open', it counts day until the next closed, or until today.
ProcessedData:
LOAD
TicketId,
Status,
Date,
If(Status='Open'
,If(Peek(TicketId)=TicketId and Peek(Status)='Close'
,Peek(Date)-Date
,Today()-Date)
,0) as DaysOpened
Resident InitialData
Order by TicketId, Date desc;
With this you can do a group by TicketId and sum(DaysOpened) to count how many days each ticket has been in Open state.
Thanks it works like a charm.. I am bowled over the logic.. its very neat and well thought..
Highly envious on that though!!!
Thanks again