filters in custom metric not working properly

3 Comments

  • Oliver Knigge

    Update:

    Now I have tried the following recipe:
    https://support.zendesk.com/hc/en-us/articles/229244588-Reporting-on-ticket-tags

    First I have created the metrics to differentiate between the tags:

    • # Group1-Tickets without tag1 or tag2
      SELECT IFNULL((SELECT # Solved Tickets WHERE Ticket Group = Group1 AND Ticket Tag NOT IN (tag1,tag2)),0)

    • # Group1-Tickets with tag1 or tag2
      SELECT IFNULL((SELECT # Solved Tickets WHERE Ticket Group = Group1 AND Ticket Tag IN (tag1,tag2)),0)

    • # Group5-Tickets without tag3
      SELECT IFNULL((SELECT # Solved Tickets WHERE Ticket Group = Group5 AND Ticket Tag NOT IN (tag3)),0)

    • # Group5-Tickets with tag3
      SELECT IFNULL((SELECT # Solved Tickets WHERE Ticket Group = Group5 AND Ticket Tag IN (tag3)),0)

     

    Then I have updated the # Solved Tickets (Dep1) and (Dep2) metrics with the new created tag-metrics 

    • # Solved Tickets (Dep1)
      SELECT # Solved Tickets WHERE ((SELECT # Group1-Tickets without tag1 or tag2 BY Ticket Id)=1) OR (Ticket Group IN (Group2, Group3, Group4)) OR ((SELECT # Group5-Tickets without tag3 BY Ticket Id)=1)

    • # Solved Tickets (Dep2)
      SELECT # Solved Tickets WHERE ((SELECT # Group1-Tickets with tag1 or tag2 BY Ticket Id)=1) OR (Ticket Group IN (Group2, Group3, Group4)) OR ((SELECT # Group5-Tickets with tag3 BY Ticket Id)=1)

    But that shows me the same results.
    Now I have added the ticket group (How?) to the test report to check how the tickets are counted exactly. 

    And now I could see that # Solved Tickets and # Solved Tickets (Dep1) counts the same amount of tickets for group1 and group5. I absolutely do not understand, why or how this is happening. This shows me, that the filtering by tags is not working, especially tickets with excluded tags. Filtering only by ticket group also seems to work (Group2, Group3, Group4). But once I try to exclude some tags, something goes wrong. 
    It is also really strange that # Solved Tickets (Dep2) seems to work as expected.

    Filtering by included tags seems to work (Dep2). 
    Filtering by excluded tags seems not to work (Dep1). 

    I am really sorry for my bad englisch. Hopefully you can understand the problem :)

    Thanks again!

    0
  • Amy Dee
    Zendesk team member

    Hi Oliver! You're on the right track. The challenge here is that one ticket may have any number of tags at the same time, and those tags are all stored together.

    Due to the way tag data is stored, every ticket will have at least one record for Ticket Tag that does not match the one in your filter. This means you can't use "IS NOT" or "NOT IN" when reporting on tags. Those filters just return all tickets. That is probably what happened in your report.

    Instead, you need to create tag filtering metrics that count 1 when a tag is present and count 0 when the tag is not present. To keep your filters consistent, I recommend building your tag filtering metrics separately, without combining them in your other metrics.

    In your case, I would start with these two metrics:

    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tag IN (tag1tag2)),0)
    • SELECT IFNULL((SELECT # Tickets WHERE Ticket Tagtag3),0)

    Once you have those metrics, you can add them to your solved ticket metrics as filters:

    Group1 solved tickets without tag1 or tag2

    • SELECT # Solved Tickets WHERE Ticket GroupGroup1 AND (SELECT Tickets with tag1 or tag2 BY Ticket Id)=0

    Group2 solved tickets with tag1 or tag2

    • SELECT # Solved Tickets WHERE Ticket Group = Group1 AND (SELECT Tickets with tag1 or tag2 BY Ticket Id)=1

    Group2 solved tickets without tag3

    • SELECT # Solved Tickets WHERE Ticket Group = Group2 AND (SELECT Tickets with tag3 BY Ticket Id)=0

    Group2 solved tickets with tag3

    • SELECT # Solved Tickets WHERE Ticket Group = Group2 AND (SELECT Tickets with tag3 BY Ticket Id)=1

    This should make your results more consistent. It also allows you to report on these tags in other reports, which gives you some flexibility.

    I hope this helps! Happy reporting!

    0
  • Oliver Knigge

    Thank you so much Amy! Awesome - finally that helps :)

    0

Please sign in to leave a comment.

Powered by Zendesk