Calculating a daily unique average, when some tickets appear in multiple distinct days


  • Hannah Meier
    Zendesk Team Member

    Hi Saxon Clay

    The problem with this query where it is strictly counting unique ticket IDs and excluding tickets that were worked over multiple days is because you're using the D_COUNT() aggregator to count these tickets. 

    The D_COUNT() aggregator counts all distinct values. 

    The COUNT() aggregator counts all values (so it will count the duplicates you described above)

    For more information about metric aggregators, you can see this article.

  • Saxon Clay

    Hannah Meier thank you for the response!

    Our agents will send multiple replies per ticket, so if I'm using COUNT(Tickets updated w/public comment) I will get multiple instances of the same Ticket ID per day, to the point that I'm instead counting how many public comments an agent made per day instead of how many unique tickets were worked which still throws off our results. 

    I need to specifically retrieve the unique ticket IDs worked on a per-day basis, and then average those counts for the full year. 

  • Elzbieta Petryka

    Hi Saxon Clay

    I wonder if you have found a solution to this question as I have a similar problem..


  • Saxon Clay

    Elzbieta Petryka I actually did find a good workaround to this issue after some significant digging around. 

    The piece of the puzzle that allowed us to start calculating this was Explore supporting Joint Attributes

    What I ended up creating was a custom joint attribute that combined the Ticket ID of a given ticket, as well as each unique Update - Date where the ticket was updated. These custom measures were made in the Ticket Updates dataset:

    Custom Attribute - "Dates per Ticket"

    [Ticket ID]+"-"+[Update - Date]

    Once this attribute was in place, we could then translate it into an attribute using DCOUNT_VALUES:

    Custom Metric - "Dates per Ticket - Metric"

    DCOUNT_VALUES([Dates per Ticket])

    Once I had this metric I was able to calculate the tickets that a given agent worked on a given day, and then calculate the number of tickets they worked the next day even if the TICKETS were the same, because the attribute would be different. The Ticket ID stays the same, but the Update - Date piece of the joint attribute would increment and allow it to be counted separately. 


    I hope this helps!


Please sign in to leave a comment.

Powered by Zendesk