Explore Query - Solved ticket within 1 week

1262074037710

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??

 

Hi Adrian,

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!

Graeme Carmichael you bet. Can't take credit for it myself but happy to keep it circulating :) 

 Adrian Bishop - 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. 

Stephen

That is an excellent tip for using the date format attribute. Thank you!

 

 

10205483067

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?

Hey 20024794147 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 :)