In this Explore recipe, you'll learn how to find only tickets that weren't closed by merge.
What you'll need
Skill level: Intermediate
Time Required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
How to create the custom metric in Explore
All merged tickets have the tag closed_by_merge. You can use that information to exclude tickets that were closed by merge by creating a standard calculated metric in your report. Use the following steps to create this report in Explore.
- In Explore, click the report (
) icon. - In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
- In the Calculations panel of the report builder, select Standard calculated metric.
- Enter a name for your custom metric, for example, "Exclude closed by merge".
- Add the formula below and select Save after.
IF (NOT INCLUDES_ANY([Ticket tags], "closed_by_merge")) THEN [Ticket ID] ENDIF - In the Metrics panel, click Add, expand the Calculated metrics folder, and select the metric you just created.
- Change the metric's aggregator to D_COUNT.
For more recipes on reporting with tags see this article: Reporting with tags.
19 Comments
Wouldn't adding a filter to exclude the Tag 'closed_by_merge' be as accurate in getting this job done?
Hi Ahmad,
Unfortunately, you can only exclude tickets closed by merge by following the recipe above.
Thanks all. So what do we do with the metric when it's completed? Do we have to add it to all the queries that have been created?
In our case we want to use the awesome default dashboards and queries but all those reports should exclude tickets with the closed_by_merge tag. So in this scenario do we:
Then all queries should show the correct 'filtered' data?
Seems like a lot of work. Surely filters, like a dashboard filter, should be able to do this? Otherwise why would I ever use a filter and, filtering results becomes a huge undertaking each time. Just can't wrap my head around filtering...and don't get me started on Drill-in :P
Thanks
Chad
I also created this custom metric in my reports but how to I utilize this so that it automatically removes these tickets from my retention rate for instance? I don't want the closed by merged tickets to be taken into consideration in retention since a dropped chat for instance will continue their retention when the customer returns to the chat.
Hi Chad,
You can create a custom attribute as described here: Using INCLUDE in a standard calculated attribute. You formula should look something like this:
You should then be able to add this custom attribute as a data filter in your dashboard, and filter all query widgets to exclude merged tickets.
Hi Jamie,
When you use the custom metric described in this guide in your query, then the results displayed in your report will already have the merged tickets excluded. If you have multiple metrics in your report, then it may be better to use a custom attribute filter, similar to the sample above.
I followed the steps above, but my results still show tickets that are closed by merge.
Solution: I went to Result Manipulation > Metric Filter > Checkbox for "Hide Blank Value"
Carl McDowell - I am confused by steps 7 & 8. Do you mean to change the name I just created (Exclude closed by merge tickets) to D_Count? Or, are you saying that I should replace D_Count with what I just did? When I created a query with the calculation I just created (instead of D_Count), my results included closed by merge tickets. And, the solution that Joshua offered was not available to me. Could you please further explain the final steps?
When you select your calculated metric to the query in Step 7, it will be listed as SUM(Exclude closed by merge). For step 8, click on the metric, so that you get the popup listing different aggregation options. Scroll down if necessary and select D_COUNT instead of SUM.
Hope that helps!
Yes, that helped! And, I realized that I actually needed to follow Gab's instructions to create an attribute instead of metric. So, for others on here - if you're trying to create a query that hides tickets with the 'closed_by_merge' tag, you'll want to create an attribute vs. a metric using Gab Guinto's code. In my case, my primary filter was D_Count for all of my tickets, and then one of my rows was the closed by merge attribute. I then filtered that row to only return "not merged." Hope that helps others who got a little lost like me!
this worked for me however it shows of all time, is there a way to show just the last 30 days the amount of tickets solved and "exclude tickets closed_by_merge_"?
Hi Nate Campoli, you can use a date filter to show only tickets solved in the last 30 days (while still excluding tickets closed by merge). After you follow the recipe above, do the following additional steps:
Hi there - do you have an explore recipe for how to do this for SLA achieved tickets?
SLA date is in a separate dataset -- here's a recipe to get you started: Explore recipe: Reviewing SLA performance
Hi!
I´ve been trying to exclude closed by merge tickets from my views in a different combination of ways now, and I don´t seem to be able to find a way that completely works.
I tried this Calculated Metric and I still see tickets that were closed by a merge in the view. I made a couple validations for this weekend, and the list of shown below, even though they do have the tag: closed_by_merge, are in my view.
I already tried the Result Manipulation instructions provided by Joshua and it just don´t want to work in any way.
Can you please tell me what am I missing?
I'm having a terrible time implementing the above, can someone walk me through this like I'm a 5 year old?? I have to show a report for Full Resolution Time (Business Hours), from the previous month. That said, the merged tickets are skewing my results because users are re-opening old tickets prior to merge and the end result is some tickets with 1000+ hours until full resolution.
How do exclude merged tickets from a specific report in my dashboard?
Bonus points if you can provide me with copy/paste language for any custom attribute I need to create. Thank you!
Hi, What if I wanted to do the opposite and have a report that shows me all merged tickets. What is the best way to do that?
Hi Alyssa Douglas welcome to our Community!
You can follow the instructions above but instead of using the function "NOT INCLUDES_ANY", you can use "INCLUDES" or "INCLUDES_ALL" :)
Hope this helps!
Please sign in to leave a comment.