Explore recipe: Reporting on created and solved tickets

Return to top

28 Comments

  • Casey Moore

    This article is really helpful, thank you!

    Does anyone know how I can find the following:

    1. Tickets created yesterday (this one I have)
    2. Of those tickets, how many were solved?
    3. Of those solved tickets, how many were one-touch?

    This is what I have so far but it's not quite there yet.  This shows me how many tickets were created (yesterday) compared to how many were solved yesterday.  Right?  

    So... if I have 100 tickets created yesterday, how many of those 100 tickets were solved yesterday?  Let's pretend it's 50; of those 50 tickets (which were created and solved on the same day) how many were one-touch?

    Help me Obi Wan...  You're my only hope.

     

    0
  • RJ Hines

    Hey Casey,

    I had the exact same scenario and need a few months back. The last bit you're after - the COUNT(One-touch tickets) metric - does not exist in the Ticket Updates dataset.

    If you're looking to get all three pieces of data you identified into a single visual, you'll need to use the Support: Tickets dataset.  In my example below, I added my three metrics then used filters for Ticket Created + Ticket Updated attributes, both set to 'Yesterday':

    The only real downside to this visual is we can only view one day at a time due to how the dataset's date/time filters work, but this is all we need.

    Hope this helps!

    0
  • Adam Hanna

    Does anyone have a way to do similar to what @... did above, but for a week long view instead of just daily. Looking to have tickets created by day of the week and of those tickets, how many were solved that same day - doesn't need to include one-touch tickets

    0
  • RJ Hines

    Adam,

    Without that third metric involved, you would want to use the Ticket Updates dataset instead. I added the default system metrics Tickets Created and Tickets Solved, then set the date range. In the example below, I used 'Last Week' as my timeframe, and for flavor also added a Ticket Created - Date filter to remove Saturday/Sunday's values.

    Hope this helps!

    1
  • Mark Leci

    RJ Hines I might be wrong, but I think your graph above is the same problem I'm running into, which is that instead of 'tickets created or solved on x date' you have 'tickets created on x date that were also solved'. This has been a problem for me because what I want is 'all the tickets that were created or solved on x date', i.e. the delta. If I try to do that using the ticket created date it's wrong as noted, if I use the 'date updated' the graph shows maybe 5x as many tickets for last week as it should. I haven't yet been able to figure this out. 

    1
  • MAD Monica Maldonado

    Hi Mark Leci 

    Did you get your answer?

    I am running this query and i get 40k created tickets and 50k solved ones in one day, so I am assuming (maybe wrongly) that the solved ones are not only those created in that day. Am i wrong?

    I am actually looking for the data that answer me how many unsolved Tickets i got in a specific day, and these calculations are the ones i need. So if i know how many tickets were created from the beggining until that specific date and how many tickets were solved from the beggining unitl that date i ll get the outstanding number of tickets on that specific date. 

    So far, i have been unable to get that right number.

    Let me know if you got any answer on your question please.

    Thanks

    0
  • Marco
    Zendesk Customer Care
    Hi Monica! Thanks for the comment. 
     
    I see what you mean. Basically, this would be caused by the having to choose what time filter to use (either ticket created - date or ticket solved - date). This is resolved by creating two different queries, created tickets uses the ticket created - date time filter, and the solved tickets using the ticket solved - date filter. This is how it is done in the pre-built dashboard for Support. Then it would be a matter of putting both those queries on the same dashboard. 
     
    Hope this clarifies it for you! Cheers!  
    0
  • MAD Monica Maldonado

    Thanks for the answer Marco.

    To be honest i am still not convinced. so i can see the created tickets at any point, but when talking about solved tickets in a past date, this number is not stable as it changes due to reopened tickets right? so if i want to see how many tickets outstanding i had on the 31/12/2021 i am not gonna get the same number I had if i was checking on the 31st dec. 

    I am only finding numbers for live outstanding but nothing historical. Am I wrong?

    0
  • bill cicchetti

    H Monica,

    I have had trouble with this report in the past. Support has told me to use Update-Month and Update-Year for your date restrictions.

    I did data validation and the numbers match if I run the reports date just for Total ticket Assigned (using date assigned) and Ticket Solved (using date solved). I also used in my metrics D_Count instead of Count.  

    Hope that helps

    0
  • MAD Monica Maldonado

    Thanks bill cicchetti for the time and effort. 

     

    0
  • sarah aasland

    Does anyone know if it's possible to pull up a list of the individual tickets that are being counted when you click on the KPI metric?

    0
  • AMU Cx Lead

    This query is close to what I need, but I can't figure out how to make it work for my purposes. I have an agent that works outside of our business hours on the weekends (Saturday & Sunday), and I need a query to show the following:

     - All tickets created between end  of business on Friday, to the end of Sunday.

    - What percentage of those tickets they updated to "pending" on Saturday and Sunday.

    - What percentage of the total available tickets they solved each day.

    Simply calculating how many tickets they solved isn't enough data given what we do, and only counting tickets received on the day that they work also isn't enough because of the surge of tickets we receive on Friday night.  I need to determine how many tickets they are neglecting to touch during their two-day shift so as to establish possible knowledge gaps.

    0
  • Dane
    Zendesk Engineering
    Hi Sarah,
     
    You can use the Drill-in function to show the individual tickets. More information can be found in Using drill in to refine your queries.
     
    Hi AMU Cx Lead,
     

    All tickets created between end  of business on Friday, to the end of Sunday.

     

    For this one, you can use the standard calculated metric below. In addition, don't forget to adjust the metric filter so that it will only show tickets with the "1" value.

    IF (IN([Ticket created - Day of week],ARRAY("Saturday","Sunday"))
    AND IN([Ticket created - Hour],ARRAY("0","1","2","3","4","5", "6","7","8","9","10","11","12", "13","15","16","17","18","19", "20","21","22","23")))
    OR (IN([Ticket created - Day of week],ARRAY("Friday"))
    AND IN([Ticket created - Hour],ARRAY("17", "18","19","20","21", "22", "23")))
    THEN [Ticket ID]
    ENDIF

     

    What percentage of those tickets they updated to "pending" on Saturday and Sunday.

     
    The query below shows how many tickets were set to Pending using the Metric above.
     

     

    What percentage of the total available tickets they solved each day.

     

    You can use the result metric calculation below.

     


     
    Don't forget to adjust the display format to "%".
     
    A final reminder: Please take note that we don't support building queries, dashboard and formulas from scratch. The information should give you an overview and serve as a guide on how to create one.
    0
  • AMU Cx Lead

    Dane Thank you for the reply, however, when I perform the tasks you outline and add the Ticket Status attribute, it removes "new" tickets for the results?

    0
  • Trust Payments Ltd.

    The original poster here seemed to include an image , which I'm unable to view on the post still...

    Having read through the thread, I'm still confused about how I can have the following 2 KPIs (can be simple numbers rather than graphs).  It's a simple dashboard and so I'm trying to achieve the following...

    1. Tickets created  (easy and in place using standard metric)

    2. Of those tickets in KPI 1, which are currently SOLVED or CLOSED.

    Everything I try seems to give me total crated / solved on any given time period, rather than KPI 2 being a subset of KPI 1.

    Hope that's clear and that someone can help :-)

     

    0
  • Dane
    Zendesk Engineering
    Hi Paul,
     
    Can you check Explore recipe: Reporting on created and solved tickets if it will work for your use case? 
     
    Cheers!
    0
  • Jordan Forsythe
    Zendesk Luminary

    This report isn't great in the real world when its going to include the ticket created metric for every solved ticket during my time range. This results in a massive graph spanning back possibly years depending on when the report time range of the solved ticket was created. :( 

     

     

    0
  • Judd Higgins

    Dane

    I am trying to get the average number of tickets created on the weekend.


    When I use the formula you posted in the Comments above,

     

    IF (IN([Ticket created - Day of week],ARRAY("Saturday","Sunday"))
    AND IN([Ticket created - Hour],ARRAY("0","1","2","3","4","5", "6","7","8","9","10","11","12", "13","15","16","17","18","19", "20","21","22","23")))
    OR (IN([Ticket created - Day of week],ARRAY("Friday"))
    AND IN([Ticket created - Hour],ARRAY("17", "18","19","20","21", "22", "23")))
    THEN [Ticket ID]
    ENDIF

    and I choose (AVG) I get wildy crazy numbers in the tens of thousands.

     

    Any idea how I can get the average tickets created each Friday evening, Saturday and Sunday over the course of the year?

    0
  • Dave Dyson
    Hi Judd -
     
    Did you see Dane's note in their comment?
     

    In addition, don't forget to adjust the metric filter so that it will only show tickets with the "1" value.

     
    For more information, see Selecting the metric result range
    0
  • Dave Symonds

    Hi

    I am trying to get a single report table of a count of tickets solved today and yesterday by assignee.  I cant see how to add in the second column (yesterday) to the table.  The filter based on year  against solved seems to only be able to be used the once

     

    0
  • Spencer Hutton

    Hi Dave. With your filter for Ticket solved - Year (or Date), why don't you choose Edit date ranges > Advanced and select "from the beginning of Yesterday" to "the end of Today". Then in the Columns, section, add Ticket Solved - Date.  With Assignee in the rows and the report set to Table, this sounds like it will give you what you want.

    0
  • Dave Symonds

    Hi

    Sorry - not sure what I was thinking there - what about including tickets open for a set of dates (in this case yesterday and today) as another column.

    Or

    Trying to report on the count of assigned tickets by ticket status for assignees for two selected days and showing the two dates as columns

    0
  • Dave Symonds

    Also how do you report by date on a status change - I want to show those that were open or solved by assignee for two identified dates say 5/11 and 7/11

    0
  • John DiGregorio

    Dane I have been using this report for a while now and noticed last week that I am off by a few records on numerous customers.   I have tweaked my report and found there have been 72 cases open that have 0 value for created.  The problem appears to have started in November and resolved itself in December.   Can you please help me understand what happened.  I looked at the history on the ticket and they all look fine.   Again, they show up fine on regular ticket reports but not the history report

     

    0
  • Zsa Trias
    Zendesk Customer Care

    Hello John,

    We would need to look into your account to see what's causing "0" to be returned for "Tickets created" metric on those tickets. I will create a ticket for this, please expect a separate email for the ticket that I am going to create. Thank you.

    0
  • Sara del Rosario

    Hello,

    I am looking into creating a report where I can see:

    • how many tickets were created each day of the week,
    • and how many tickets were solved each day of the week.

    Does someone have a solution for this scenario? With this Explore recipe, I am seeing all tickets that were updated each day of the week, so the numbers are inflated. I am seeing tickets that were created last month but updated further on.



    0
  • Gabriel
    Zendesk Customer Care
    Hi Sara,

    I hope all is well! In this case, you may consider using the Tickets Dataset instead of the Updates History Tickets Dataset. An example of this dataset being used can be found in this article

    I hope this helps!
    0
  • Julia DiGregorio

    Zsa Trias Kevin Artaud we are having another issue with the update history report.   I created a great report that shows open, solved, not solved, and pending month over month.   Aside from not being able to report on API Tickets (I have an issue open with support), the report appeared to work and I had planned on sharing it on this site.  However, I found a new issue.    If you do a straight report of tickets created versus solved the solved count is not correct. It appears it is not counting some of the closed tickets.   This is an out of the box report with no custom fields or other elements.  

     

    0

Please sign in to leave a comment.

Powered by Zendesk