Writing a query in Explore which filters by year and reports by month on multiple fields.

5 Comments

  • Kay
    Community Moderator

    Hi 👋

    You should be able to do this by adding a calculated metric. Then you can still apply the filters.

    Here's the pasted query

    IF ([Changes - Field name]="status" 
    AND [Changes - Previous value]!="solved"
    AND ([Changes - New value]="solved" OR [Changes - New value]="closed")
    AND ([Ticket status - Unsorted] = "Solved" OR [Ticket status - Unsorted] = "Closed")
    AND [Update - Timestamp]=[Ticket solved - Timestamp]
    AND DATE_DIFF(
    [Update - Timestamp],
    [Ticket created - Timestamp],
    "nb_of_days") <= 30
    )

    THEN [Update ID]
    ENDIF

    The query should look something like this

    1
  • Martin

    Thanks Kay, that was awesome - really I wish this kind of info was documented (or if it is, it was more available).

    The query worked but there was an additional twist. The time taken to resolve the ticket should exclude the time the ticket was in an on-hold status.

    I tried:

     "nb_of_days") - VALUE(On-hold status time (days))<= 30

    but ended up with 0 tickets.

    Is that feasible within the query?

     

    thanks!

     

    0
  • Martin

    I would also like to add that this will exclude tickets updated with a CSAT after bring solved, since the last update time will <> solved time, breaching the condition:

    AND [Update - Timestamp]=[Ticket solved - Timestamp]

    I assume that was there to get the LAST solved date, but does have this flaw.

     

     

    0
  • Kay
    Community Moderator

    To your first question, yes that's feasible. It simply could be that there are no tickets that match those conditions.

    About the CSAT, the CSAT doesn't affect this metric, except when there is some reason that the ticket changes status to other than solved.

    0
  • Martin

    Thanks for your response, Kay.

    I am not sure why the values do not match up but yes, some includes tickets also had CSAT so that is rules out.

    I did some troubleshooting and removing the line AND [Update - Timestamp]=[Ticket solved - Timestamp] did include the missing tickets but also brought back in additional tickets (or count at least) that should not apply.

    Regarding the exclusion of on-hold time, it should not reduce the counts to 0 so does not work as expected.

     

    I will continue to look into it, hoping to find differences between tickets that work and those that do not.

    thanks

    0

Please sign in to leave a comment.

Powered by Zendesk