Choosing metric aggregators

Return to top


  • Nick Lamb

    I'm working on moving our OKRs over to Explore from Insights (GoodData) and have hit a blocker. Does anyone have a good way to do this in Explore? I'm looking to recreated the following calculation for variance on MTTR. This was my attempt to move this to Explore. 

    SELECT (SELECT PERCENTILE(VALUE(Full resolution time (hrs)), 0.75)) - (SELECT PERCENTILE(VALUE(Full resolution time (hrs)), 0.25)

    The original query was:

    SELECT (SELECT PERCENTILE(Full resolution time in minutes, 0.75)/60 WHERE Ticket Status <> Deleted) - (SELECT PERCENTILE(Full resolution time in minutes, 0.25)/60 WHERE Ticket Status <> Deleted)
  • Amy Dee
    Zendesk Customer Care

    Hi Nick! At this time, Explore does not have a PERCENTILE function within metrics. You can review available metric functions here: Explore functions reference.

    The closest option is the "Percentile" pattern under Result Path Calculations. The details are here: Using result path calculations.

    It's possible to get the values for the 75th and 25th percentiles in Explore. You can't manipulate them, like finding the difference or incorporating those numbers in larger charts. You can find and display them, though, which is a start.

    To display those percentile values:

    1. Start a new query in the Tickets dataset
    2. Under Metrics, add two versions of Full Resolution Time. This can be two aggregations of the same metric, like MED and AVG resolution in minutes, or the hours and minutes versions with the same aggregation. It will be sliced by ticket and the second column will be displayed as a percentile, so the details don't matter too much at this step.
    3. Under Filters, add a filter for Status solved/closed. If you don't do this, unsolved tickets will skew the results.
    4. Under Filters, add any other relevant filters, like solve dates, groups, etc.
    5. Under Rows, add Ticket ID. This lists each value individually, which allows Explore to find the percentile within the query results.
    6. Go to Result Manipulation > Result Path Calculation, and add Percentile on rows to one of your resolution metrics. 
    7. Go to Result Manipulation > Metric Filter, and set a range of 25 to 75 on the percentile metric.
    8. Go to Result Manipulation > Top/bottom, and select the top and bottom 1 for the percentile metric.
    9. Go to Result Manipulation > Order of result manipulations, and make sure the order is Measure Calculations first, then Value Filter, then Top and Bottom.

    The result is the 75th and 25th percentiles, displayed with their resolution time and associated ticket IDs.

    Here are screenshots from Insights and Explore in a test environment, so you can see the values line up. Explore is using the method above, while Insights is using SELECT PERCENTILE(Full resolution time in minutes, ##)/60 for its metrics.

    Explore isn't as robust as Insights when it comes to percentiles (for now), but there is an option available.

    I hope this helps! Happy reporting!

  • Jonathan Molina

    Hi all, 

    We are using a custom metric that saves the timestamp of every change of "Step" (step 1 to step 2 to step 3, etc.) for every ticket.

    Now we want to obtain the Median but only of the MIN values of this metric, is that possible? The idea is to be able to calculate for every organization that we have the Median value of that first change of "Step". 


  • Jan Schweigerer

    Hi Team, 

    I want to track the pending status time during which a ticket had the custom value "issue_status_needs_investigation" assigned. 

    In your reference here, you say value aggregators can only be used within calculated metrics. 

    If I try to use the following formula (which I adjusted from your help center article for field changes time it tells me I can't use the aggregator VALUE in calculated metrics, which is exactly the opposite of what you say here above in this article

    IF ([Changes - Field name]="Issue Status")
    AND ([Changes - Previous value] = "issue_status_needs_investigation")
    THEN VALUE(Pending status time (min))

    The following formula, which is derived from your article, also uses a VALUE aggregator and is accepted.

    IF ([Changes - Field name]="Issue Status")
    AND ([Changes - Previous value] = "issue_status_needs_investigation")
    THEN VALUE(Field changes time (min))/60

    Can you explain or ideally, tell me how I could track what I want with a formula?


  • Mark Nino Valencia

    Is there a way, in a dashboard, I can switch the aggregate of a report? For example, the first reply time from AVG to MED or an option to switch between reports

  • Mike dela Rosa
    Zendesk Customer Care
    Hi Jan!

    If the metric isn't being accepted, have you tried converting it to an attribute? Explore recipe: Converting between metrics and attributes

Please sign in to leave a comment.

Powered by Zendesk