Explore Query - Solved ticket within 1 weekBeantwortet
Hi, I'm looking to create a metric for solved ticket performance, I want to be able to display the percentage of tickets which were solved within 1 week, in any given week, e.g. in a given week, if I had 10 tickets created and solved 9, therefore leaving 1 still unsolved, I want to report 90% success rate.
I'm struggling to come up with a formula to produce this, any ideas please?
Once I have it, I want to create a table and a graph which shows the performance for the previous 6 weeks.
Hey @... give this a try:
1. Create query in tickets dataset
2. Add filter: ticket created date = from x weeks ago to 1 week ago
3. Add metrics COUNT(Tickets) and COUNT(Solved Tickets)
4. Add row Ticket created - week of year (i prefer to use a custom attribute that shows the start date of that week instead of the week number:
DATE_FORMAT(START_OF_WEEK_ISO([Ticket created - Date]),"YYYY-MM-dd")
5. Go to Result manipulation > result metric calculation, and enter:
COUNT(Solved tickets) / COUNT(Tickets)
Give it a name such as % Solved Tickets and add.
6. Go to Chart Configuration > Display format and switch your % Solved Tickets calculation to display as %.
Your result should look something like this:
Hope that helps get you started :)
Thanks Stephen, thanks for answering Adrian's question! We'll be sending you a token of our appreciation :)
Thanks for your advice here, unfortunately, it's not quite what I'm looking for, I already managed to produce a similar result but what I can't do is produce a report which shows solved ticket performance. Let me try to explain, with the current table, I show an example below from a few months ago, the tickets from those weeks are now solved which means the score is 100%, what I need to produce is how many tickets we are creating in a week VS how many tickets we are solving in that same week, it's like I need to filter the tickets column by created date, and the solved column by solved date separately, then divide to get the percentage. The score will likely fluctuate depending on our ticket volume and how many agents we have working etc, once I have the metric working, I will break the result down by group and hopefully, it will highlight who is managing their workload. Hope that makes more sense?
That is an excellent tip for using the date format attribute. Thank you!
@... you bet. Can't take credit for it myself but happy to keep it circulating :)
@... - Gotcha! That makes perfect sense. In that case, we need to recreate this query in the Ticket Updates dataset. Then, as we look at # Tickets Created / # Tickets Solved metrics, if we use the "Update - Date" attribute as the date filter, it will look at the date of the create/solved event for that week.
EDIT: Just make sure you are still using Created - Week of year as your row. So Update - date is only for filter.
Have you tried making a new metric for tickets solved within 7 days? It should look something like this:
IF (DATE_DIFF([Ticket solved - Date],[Ticket created - Date],"day") <= 7) THEN [Ticket ID] ENDIF
From there you can follow Stephen's instructions from step 5 to divide this by the total ticket count and display as a percentage.
Note that this shows tickets solved within 7 days of creation - I'm not sure if you want this or how many tickets still remained unsolved at the end of a given week.
If you want the latter, I'll go back to the drawing board - I think it's still not that bad using one of the WEEK_NUMBER formulas depending on when your date starts - but I wanted to check if this is what you needed first!
Thanks, this has given me what I need and I'm able to produce a suitable table. I now have my next challenge, I need to get this data into a column chart! Due to the % calculation being obtained from result manipulation, I cannot create a graph from this? I tried to create another metric that I could use for my graph but it does not deliver the results I need? Any ideas on how to achieve this, please?
Solved % is my result manipulation, solved ticket % is my attempt at a simple metric, the latter delivers strange results??
In order to best assist you I created a ticket for your question.
You should receive an email shortly about it, and we will be able to proceed there.
Thibaut | Customer Advocate | EMEA
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.