Equalize pending and solved tickets
Hey!
Due to our internal ticket handling flow I'd like to create a metric similar to "# Tickets solved" but which contains not only solved / closed tickets, but also pending tickets.
Here my baseline for the metric, "# Tickets solved":
SELECT IFNULL(COUNT(Ticket Id, Ticket Text Field Change), 0) WHERE [Text Field] New Value IN ([Status] solved, [Status] closed) AND [Text Field] Previous Value <> [Status] solved AND Ticket Status IN (Solved, Closed) AND Date (Event) = (SELECT MAX(SELECT Date (Ticket Solved) BY Ticket Id) BY Ticket Id)
My approach for adding the pending tickets to this:
SELECT IFNULL(COUNT(Ticket Id, Ticket Text Field Change), 0) WHERE [Text Field] New Value IN ([Status] pending, [Status] solved, [Status] closed) AND [Text Field] Previous Value NOT IN ([Status] pending, [Status] solved) AND Ticket Status IN (Pending, Solved, Closed) AND Date (Event) = (SELECT IFNULL(MAX(SELECT Date (Ticket Solved) BY Ticket Id), Date (Ticket Last Updated) BY Ticket ID) BY Ticket Id)
However, when I try to save this I get the following error message:
Unexpected 'Date (Ticket Last Updated)'. Expecting: -, ZERO, NATURAL, REAL
After trying different approaches I don't know how to fix this last condition starting with "... AND Date (Event) = (SELECT ..."
Any help with this would be much appreciated.
-
Pawel
So this is tricky.
To correct the metric syntax, duplicate the original metric, then change one element at a time, save the definition as you go to highlight where it has gone wrong. Hopefully, if you do that, nothing will go wrong and the definition will look like this:
Ensure the colours match and as I am sure you know, each attribute and value must be selected from the element selector on the right hand pane rather than just typed in.
The tricky part is, this will not select any tickets that are currently sitting at status pending. The final AND condition ensures only solved and closed tickets make it:
So you have to remove that condition from the end to give:
Now, we may be able to stop there. So I would test that definition and check that you get expected results.
-
Hello Graeme!
Thank you for your help with this, I've tried it out now and will monitor the numbers to verify whether the outcome is as expected.
I've adapted the metric a bit by excluding the Closed tickets as otherwise they'd mess up the numbers a bit (eg when an agent handled 100 tickets yesterday but another 20 tickets have been automatically put from solved to closed, they've been added to the count as well - not sure why as
"[Text Field] Previous Value NOT IN ([Status] pending, [Status] solved)"
should have prevented it).
In regards to your solution of removing the last condition, I thought that it would be necessary to get the right event date for this metric, therefore I tried to somehow adapt it, but at first glance at least it doesn't seem to distort the results.
I'll monitor it for couple of days and get back to you next week to verify whether it worked out or not.
Thanks again for your help Graeme!
Best,
Pawel
-
Pawel
Sounds like you are on the right track.I am not going to be around for a while, so I hope you get sorted.
Please sign in to leave a comment.
3 Comments