topic No of days with multiple reopen dates in App Development

No of days with multiple reopen dates

anushree1 — Tue, 08 Nov 2022 10:46:04 GMT

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

Re: No of days with multiple reopen dates

rubenmarin — Tue, 08 Nov 2022 11:41:22 GMT

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.

Re: No of days with multiple reopen dates

anushree1 — Tue, 08 Nov 2022 16:54:19 GMT

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