Insights recipe: Reporting on created and solved tickets Follow

Comments

13 comments

  • Avatar
    Simon Peach

    Hello,

    I've been able to build this metric to allow me to build reports covering up to 4 months of data using date (event), I'd like to use this metric to build a 12 month view showing points for each of the 12 months. I can not find a month of year attribute and using the month(event) attribute only returns four months of data.

    Where am I going wrong?

  • Avatar
    Brent

    @Simon I've looked into this issue a little bit and I've determined it is an issue specific to your Insight project, so I'm going to make a separate ticket and work with you in there.

  • Avatar
    Intis Kalnins

    Hi Guys,

    As far as I understand a "solved" ticket is only counted when a comment is present. I'd also like to include tickets solved without comment in the custom metric above and I guess I would just add something like Public Comment present = (true, false), but where would I position that in the full metric?

    Thanks

  • Avatar
    Brian Manning

    @Intis- The '# Solved Tickets' metric described in this article isn't dependent on comments. It will pick up the last event where a ticket was set to solved regardless of the comments on the ticket.

    If you're having trouble with the behavior of your implementation of this metric let me know and I'll spin up a ticket for you.

    Cheers!

  • Avatar
    Ping Lu

    Hi,

    I'm new to Insight and trying to create a report on the number of tickets that are open by month/year, along with # tickets created and solved. The problem is that there might be no event happening on an open ticket throughout a month, thus the How of Date(Event) doesn't work. Any thoughts?

    Thanks

     

     

     

  • Avatar
    Dennis Lynn

    Hey Ping,

    If you are just looking to see the number of tickets that are open as of a specific day, you can take a look at the backlog dataset and either use it in your own report, or use the prebuilt Insights report which should give you what you're looking for. The trick with this dataset is that the backlog only gives you the number of tickets in a given status at the end of a given day, so slicing it by month or something like that won't work either. You can find more information on this dataset here:

    Using the Backlog Dataset in Insights (Professional and Enterprise)

    You can also use the same data set to give you the number of tickets in any other status. Good luck, and happy reporting!!

  • Avatar
    Ping Lu

    Hi Dennis,

    Thanks a lot for getting back to me. While trying the backlog dataset, we found it's hard to understand the relationship between backlog and other ticket counts like # Tickets Created / Solved / Deleted / Reopened. They don't seem to add up. It seems to add more complicity if we add ticket priority as a filter, as the backlog priority isn't necessarily the ticket priority as the latter is the current value.

    We will ask for more help by opening a ticket. Many thanks.

     

     

     

  • Avatar
    Caleb Strawn

    When I try to create this report, the "Date (Event)" is greyed out. When I force select it, the report won't pull up due to a "nonsensical data" error.  I'd like to build this report for the current week and for the current month but because I can't select any of the "event" based ways in the "how" section of the report builder. Looks like it's effectively forcing me to choose one or the other (ticket creation or ticket solution) to associate a timeframe to.

    Any help here is much appreciated!

  • Avatar
    Jacob J Christensen

    Hi Caleb,

    I just tried adding the 2 metrics # Tickets Created and # Tickets Solved and I'm allowed to select from the event attributes.

    Could you try to create the report again and make sure the right metrics are selected? I sometimes have metrics that are similarly named.

  • Avatar
    Rachael W

    I'm having the same trouble as Caleb, but I don't have the # Tickets Solved metric, only # Solved Tickets, which doesn't yield the results described above and still has Date (Event) greyed out. See attached photos 

  • Avatar
    Jacob J Christensen

    Ah, OK. Maybe the # Tickets Solved was a custom metric, I seem to recall having to do that one manually. But the original article by Hammad doesn't seem to exits anymore. I don't know what the reason for that might be.

    My # Tickets Solved metric looks like this (take a deep breath):

    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)

     

    Metric format: #.##0

    I find the easiest way to create a custom metric is by pasting in the whole text and then select the Attributes (purple) and attribute values (orange) one by one, and save.

    Sorry to Caleb if i missed what this was really about.

  • Avatar
    Caleb Strawn

    Okay, just now getting back to this. I've created a new custom metric for #of solved tickets using the MAQL formula you suggested which DID allow me to select Date (Event) however, that custom metric isn't pulling the numbers I'm looking for. It's actually not returning any numbers at all. I can assure you we solved tickets last month.. :)  Take a look at these screenshots. Maybe you can identify what's wrong with my metric.






  • Avatar
    Jacob J Christensen

    Hi Caleb,

    It looks like we don't have the same attribute values, I'm guessing this could be why.

    Notice (above) than my attribute values for status are [status] Solved, where yours are merely Solved.

    To find that I search for the New Value attribute in the Attribute value folder, click the right one and again search Status. Now I can select the [status] solved attribute value and the others as well.

    Hope that makes sense, and solves your issue.

Please sign in to leave a comment.

Powered by Zendesk