When you filter your insight result, you filter for any of the values in the filter definition. For example, if you work with support tickets and their tags, filter for tags A, B would return all tickets tagged with A or B (or both).
What if you want to return only the tickets tagged by both A and B?Â
The simplified data model can consist only of two datasets, one for tickets and one for tags. One ticket can be tagged by multiple tags and one tag can be used for multiple tickets, so there is a many-to-many relationship between the two datasets.
In our example, we would have just 5 tickets (1-5) and 5 tags (A-E) like this:
We will now build an insight that returns the number of tickets tagged by all tags we list in a filter. For example, when we filter for tag A, the insight would return 3 (tickets 1, 2, and 5). When we filter for tags A, B, it would return 2 (tickets 2 and 5). If we set the filter to all, it would return only 1 (ticket 5 is tagged by all tags).
Our solution works for any number of tags and any tag names.
The idea behind the solution is to calculate the count of tags. If we filter for tags A, B, and C, the count of tags would return 3. If we calculate the same for each ticket, only tickets that are tagged by A and B and C would return the same value, 3.
(What if such a ticket is also tagged by tag D? The solution still works because the filter is applied to the metric as well. The metric can return values 0, 1, 2, or 3, but not more because the filter is set to 3 tags.)
Here are the two auxiliary metrics:
-
Ticket Tag Count:
SELECT COUNT(Tag) BYÂ Ticket Id, ALL OTHER -
Total Tag Count:
SELECT COUNT(Tag) BY ALL OTHERÂ Â
Now we can build the main metric that returns the number of tickets tagged by all the tags specified in the filter.
- # Tickets Tagged:
SELECT COUNT(Ticket Id) WHERE Ticket Tag Count = Total Tag Count
Here are the results:
Â
Â
The metric is sliceable, so you can show the time trend or even the specific tickets:
Â