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: 
anushree1
Specialist II
Specialist II

No of days with multiple reopen dates

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

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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.

 

View solution in original post

2 Replies
rubenmarin

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.

 

anushree1
Specialist II
Specialist II
Author

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