Question
I created a report in Explore. As I am trying to exclude ticket tags from my report, the results unexpectedly increase or change when I add a tag attribute in the Filters panel. Why do my metric values change when I filter my report by multiple tags?
Answer
If you’re not familiar with the calculations behind-the-scenes, reporting on multiple tags in the Filters panel results in unexpected behavior.
As an alternative, to exclude tickets with specific tags from your reports, create a standard calculated metric that accounts for tickets with or without those tags, or create a standard calculated attribute to use as a filter.
More explanation
The increase in metric values described above occurs because of the way Explore uses attributes to slice metric results. To understand what’s going on, let’s look at what happens when an attribute is added in the Rows panel, and then in the Filters panel.
Explore performs the same calculations when an attribute is added in the Rows (or Columns) panel as it does when an attribute is added in the Filters panel. However, the former results in a visible change to the report while the latter does not. This makes it difficult to understand what’s happening behind the scenes.
The sections below walk you through examples of this issue:
- What happens when you use the COUNT aggregator
- What happens when you use the SUM aggregator
- What happens when you use the AVG aggregator
- What to do instead
What happens when you use the COUNT aggregator
When a tag attribute is added to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute has been added to the Rows panel and filtered by two values (“apple” and “banana”). You can see that there are two tickets, each of which has both tags. Explore breaks the results out into three columns: the first shows the two tickets, the second shows the two tags (repeated for each ticket), and the third shows that each tag is present on each (1) ticket.
However, if you remove the Ticket ID attribute and move the Ticket tags attribute to the Filters panel, the results look like this: 
In this report, the Ticket tags attribute is still filtered on the “apple” and “banana” values. Here, Explore is calculating the number of tickets that each tag is added to (2 tags x 2 tickets = 4 tickets). In other words, it’s summing the values from the third column in the first version of the report. This result is not intuitive to users unfamiliar with Explore’s calculations and can cause confusion or incorrectly reported data.
What happens when you use the SUM aggregator
When a tag attribute is added to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute has been added to the Rows panel and filtered by two values (“closed_by_merge” and “org__is_trusted”). You can see that there are two tickets, each of which has both tags. Explore breaks the results out into three columns: the first shows the two tickets, the second shows the two tags (repeated for each ticket), and the third shows the full resolution time for each ticket.
However, if you move the Ticket tags attribute to the Filters panel, the results look like this:
In this report, the Ticket tags attribute is still filtered on the “closed_by_merge” and “org__is_trusted” values. But here, Explore is incorrectly doubling the full resolution time for each ticket because it's summing the value of the ticket's full resolution time for each tag.
What happens when you use the AVG aggregator
When a tag attribute is added to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute has been added to the Rows panel and filtered by two values (“closed_by_merge” and “org__is_trusted”). In this case, you can see that there are three tickets now, one of which has only one of the tags and two of which have both tags. Again, Explore breaks the results out into three columns: the first shows the three tickets, the second shows the two tags (only one of which is present on the first ticket), and the third shows the full resolution time for each ticket.
If you move the Ticket tags attribute to the Filters panel, the results look like this:
At this point, the results are still accurate. Explore is showing the correct full resolution time for each ticket because the results are being sliced by the ticket ID. But if you remove the Ticket ID attribute, the results look like this:
In this report, the average full resolution time for the three tickets is not being calculated correctly. Taking the full resolution time values from the previous report, the correct calculation should be:
- (36 + 54 + 23) / 3 = 38 minutes
Instead, Explore is including the full resolution time values for each tag instance (similar to how it did with the SUM aggregator) for a resulting calculation of:
- (36 + 54 + 54 + 23 + 23) / 3 = 63 minutes
What to do instead
To avoid wrongly aggregated results, you can do one of the following instead:
- Create a calculated attribute. You can create a calculated attribute with a formula specifically designed to find multiple tags. For help, see the section Finding tickets with multiple tags in the article Reporting with tags.
- Use the D_COUNT aggregator. For count-based metrics (like the number of tickets), you can use the D_COUNT aggregator, which always reflects unique values. For help, see the article Choosing metric aggregators. However, this doesn’t work for time-based metrics (like first reply time) or other aggregators (like SUM, AVG, and MED).
- Filter by one tag at a time. Depending on your reporting needs, you can filter your report by just one tag at a time. However, this doesn’t work if your use case relies on finding tickets that must have multiple tags.