Question
I created a report in Zendesk Explore. When I exclude ticket tags and add a tag attribute in the Filters panel, my results increase or change. Why do my metric values change when I filter my report by multiple tags?
Answer
If you are not familiar with the calculations behind the scenes, reports that include multiple tags in the Filters panel can produce unexpected results.
To exclude tickets with specific tags, create a standard calculated metric that includes or excludes those tags, or create a standard calculated attribute to use as a filter.
More explanation
The increase in metric values occurs because Explore uses attributes to slice metric results. To understand what happens, compare what occurs when you add an attribute to the Rows panel and to the Filters panel.
Explore performs the same calculations whether you add an attribute to the Rows (or Columns) panel or to the Filters panel. Rows changes the visible report; Filters does not. That difference can hide how Explore calculates results.
The sections below show examples of this issue and explain how tags behave with the COUNT, SUM, and AVG metric aggregators:
- 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 you add a tag attribute to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute is in the Rows panel and filtered by two values (“apple” and “banana”). There are two tickets and each one has both tags. Explore breaks the results 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 ticket (1).
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 counts tickets per tag (2 tags x 2 tickets = 4). In other words, it sums the values from the third column in the first version of the report. That result can confuse users and lead to incorrect data.
What happens when you use the SUM aggregator
When you add a tag attribute to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute is in the Rows panel and filtered by two values (“closed_by_merge” and “org__is_trusted”). There are two tickets and each one has both tags. Explore breaks the results 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.
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. Here, Explore doubles the full resolution time for each ticket incorrectly because it sums the ticket’s full resolution time for each tag.
What happens when you use the AVG aggregator
When you add a tag attribute to the Rows panel, the results look like this:
In the report above, the Ticket tags attribute is in the Rows panel and filtered by two values (“closed_by_merge” and “org__is_trusted”). There are three tickets now, one ticket has only one of the tags and two tickets have both tags. Explore again breaks the results into three columns: the first shows the three tickets, the second shows the two tags (only one appears 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 accurate. Explore shows the correct full resolution time for each ticket because the results are sliced by ticket ID.
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 correct. With the values from the prior report, the correct calculation should be:
- (36 + 54 + 23) / 3 = 38 minutes
Instead, Explore includes the full resolution time values for each tag instance for this calculation:
- (36 + 54 + 54 + 23 + 23) / 3 = 63 minutes
What to do instead
To avoid incorrect aggregation, choose one of these options:
- Create a calculated attribute. Use a formula that returns tickets with multiple tags. For help, see Finding tickets with multiple tags in Reporting with tags.
- Use the D_COUNT aggregator. For count-based metrics, such as the number of tickets, use the D_COUNT aggregator, which returns unique values every time. For help, see Choosing metric aggregators. This option does not work for time-based metrics, such as first reply time, or for SUM, AVG, and MED.
- Filter by one tag at a time. Based on your needs, you can filter your report by one tag at a time. This option does not work for use cases that require tickets with multiple tags.