English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Performing calculations

Creating standard calculated metrics and attributes

Rob Stack
  • Edited
Zendesk Documentation Team

What's my plan?

Suite Professional, Enterprise, and Enterprise Plus

When the metrics and attributes included with Explore do not meet your needs, you can create your own custom metrics and attributes. In this article, you'll learn about standard calculated metrics and standard calculated attributes. These enable you to create new metrics and attributes using the metrics and attributes included with Explore along with a variety of formulas, mathematical functions, and more.

For a list of all available calculated metrics and attributes, see Calculation types reference. For an introduction to writing formulas, see Writing Explore formulas.

This article contains the following sections:

Creating standard calculated metrics

Standard calculated metrics enable you to create completely new metrics to add to your dataset. You can use pre-existing metrics and attributes from your dataset, and functions included with Explore's formula writing language.

For example, you could create a standard calculated metric that only returns tickets received through the email channel by using the following formula:
IF ([Ticket channel]="Email") THEN [Ticket ID] ENDIF

To create a standard calculated metric

  1. In the report builder, click the calculations icon () on the right sidebar.
  2. Click Standard calculated metric. The Standard calculated metric panel opens.
  3. In the Name field, type a name for your new metric.
  4. In the Formula field, use the Fields drop-down list to add existing metrics to your formula, or click Explore's auto-complete suggestion as you type the formula.

  5. If you want to add functions to your formula, click Add under Functions or click Explore's auto-complete suggestion as you type the formula. For a full list of all available functions, see Explore functions reference.

  6. Check Compute separately if your formula is using existing calculated metrics and attributes, and you do not want to affect their calculations.
  7. Click Save. The metric is saved to the dataset you currently have loaded.

You can now add this calculated metric to your report. When you click Add in the Metrics panel, you'll find you metric in the Calculated metrics folder.

Creating standard calculated attributes

Use standard calculated attributes to create new, custom attributes to add to your dataset. Standard calculated attributes can use existing attributes and metrics as well as functions from Explore's formula writing language.

The steps to create a standard calculated attribute are the same as the standard calculated metric instructions above, except there is no Compute separately option. There are two additional options available:
  • Computed from: The drop-down list displays metrics and attributes you can use in your standard calculated attribute.

    Important: Although you can choose standard calculated metrics from the list, this can lead to inaccurate results and is not supported. Use only the metrics supplied with Explore in your standard calculated attributes.
  • Sort like time attribute: If your calculated attribute represents dates, this option places your date values in the correct order.
    • Note: This option applies only to single attributes. Combining attributes, such as adding a year to the end of a month, will result in sorting alphabetically instead of chronologically.

When you click Add in the Columns, Rows, Explosions, or Filters panels, you'll find your attribute in the Calculated attributes folder.

Return to top

33 Comments

  • Jahn Jerenz Bronilla

    Hi Team - trying to create Same Day Resolution (SDR) Metric in Explore however, it seems that some values are not being captured.

    Should be Ticket Created Date = Ticket Solved Date like the below:


    However, there's some result that are incorrect. See sample:

    Here's the calculation I created.


    Can someone help me identify what's wrong with my calculation?

    Thank you
    Jerenz

    0
  • Bonnie
    Zendesk Customer Care

    Hi Jerenz! It looks like we were able to help you with this question via a ticket. I'll just share the metric my colleague helped you with here, in case anyone else is following along the conversation.

    IF ([Ticket created - Date]=[Ticket solved - Date])
    THEN [Ticket ID]
    ENDIF

    0
  • Jahn Jerenz Bronilla

    Hi Bonnie Leib - yes and totally helpful :) Thanks for your help.

    0
  • Gaurav Sharma

    Hi ,

    i have created a Group attribute with the agents from our group in the Support-tickets dataset .

    Is there a way i can copy the same in the other datasets .

    i want to use the group attribute as a filter in a dashboard which has different datasets ,so i i have the same attribute in all datasets the i could link and use the attribute.

    Please help!  

    0
  • Gab Guinto
    Zendesk Customer Care

    Hi Gaurav,

    You can duplicate an attribute and select which dataset you want to save it to. Here's a sample:

    Note that the attribute will only work if the elements referenced in the formula can also be found (with an identical name) in the other dataset. If, for example, your custom group is based on the native Assignee name attribute, then there should be no issue; in the Backlog dataset though, you may have to edit the copied attribute since "Assignee name" is not available in that dataset.

    0
  • Rebecca Che

    Hi, team. We have a custom field in zendesk support, so when the agent clicks the field, we can see it as true in Explore, else is False. I'm trying to use the calculated metrics to only count the True value as below but the system didn't show any data. any suggestion? 

    Thanks

    0
  • Brandon Tidd
    User Group Leader Community Moderator
    The Humblident Award - 2021

    Hi Rebecca Che,

    Very Close!  Try:

    THEN [ticket id]

     

    0
  • Catalin

    Hi guys. I'm interested in creating a custom metric to show the timestamp (or time lapsed) for the first internal note on each ticket. This used to be possible in the old system (GoodData) but I can't figure it out in Zendesk Explore. Any ideas please? Many thanks!

    0
  • Saxon Clay

    Hey Catalin, Explore has the 'DATE_FIRST' function that you can use in the Updates dataset to get the first internal comment. 

    IF ([Comment present]=TRUE
    AND [Comment public]=TRUE
    AND [Updater role] != "End-user")
    THEN DATE_FIRST([Update - Timestamp])
    ENDIF

    Is what I have for a custom 'First Update' attribute in my instance, changing 'Comment public' to FALSE and adding some separation by Ticket ID (additional column/row) should get what you're looking for.

    1
  • Catalin

    Hey Saxon, thanks a lot for the tip! I managed to get it working. Based on your suggestion, I also found some useful details (that I didn't find before) here: https://support.zendesk.com/hc/en-us/articles/4408833381402-Working-with-earliest-and-latest-date-functions#topic_hrb_wdy_5pb

    1
  • Tim Bechtold

    Hi Reporting and Analytics Team,

    I want to add 2 custom Standard Calculated Metrics to an Assignee Activity report:

    Average Tickets Solved per Hour and Average Tickets Updated per Hour. 

     

    Tim 

    0
  • Dave Dyson
    Hey Tim, welcome to the community!
     
    If you want to do this yourself, you'll need to start by cloning the dashboard -- then you can add your own metrics: Cloning dashboards
     
    If you're wanting these to be added in to the standard default dashboards in Explore, the best way to get this idea in front of our product team is to post in our Feedback - Reporting and analytics (Explore) community topic, using this template to format your feedback. Thanks!
    0
  • Saxon Clay

    Tim Bechtold You should use DCOUNT(Solved Tickets) instead of COUNT(Tickets) in the calculations. 

     

    Beyond that though, I'd recommend not using custom metrics for this and instead use Result Metric Calculation:

    This lets you run calculations on the existing metrics in your report as well as hiding source columns if desired. Since it runs off of the existing data in your query there's usually no problems with it incorrectly scoping data. 

    0
  • Dave Dyson
    Thanks for the tip, Saxon! Tim, for more information see Using result metric calculations
    0
  • Bob Gaul

    Hi,

    I am trying to add a column to a query to indicate whether an agent is an internal or external resource. This is defined by the assignee email address.

    The following formula is showing an error message when trying to add this attribute:

    IF (CONTAINS([Assignee email]]),"@example.com"))

    THEN "External"

    ELSE "Internal"

    ENDIF

    What can I do to correct this?

    0
  • Erin O'Callaghan
    Zendesk Documentation Team

    Hi Bob Gaul, I think there's an extra bracket and parenthesis after Assignee email that's causing the error. I tested the formula and it seems to work when written like this: 

    IF (CONTAINS([Assignee email], "@example.com"))
    THEN "External"
    ELSE "Internal"
    ENDIF
    0
  • Bob Gaul

    Thank you so much Erin O'Callaghan! I've no idea why I didn't think of that myself...

    0
  • MAD Monica Maldonado

    Hi there, 

    I need to create an attribute that shows me something like:

    - x requester emails  created [0-10] tickets 

    - x requester emails created [10-20] tickets

    ....

    any idea?

     i have tried a simple one like 

    if (value(End-user submitted tickets)
    >50) then ">50"
    else "<=50"
    Endif

    but it does not provide proper results. 

    Any idea?

    0
  • Walter

    Is there any way to lock calculated metrics and attributes from being altered / deleted by another user?  I think this would be a useful feature while allowing admin override.

    1
  • Dane
    Zendesk Engineering
    Hi Walter,
     
    You can create a customized dataset and just give editors the ability to view and save reports. 

     
    Users will not have access to the "Calculations" section. Even though they can still access the existing calculated metrics or attributes, any changes they made will not be saved.
     

     
    Hope this helps!
     
    1
  • Walter

    Thanks Dane!

    0
  • John DiGregorio

    Dane your suggestion for grouping orgnazations worked like a champ.  I am now stuck on how to report on month and year combined in a calculated attribute.   In this article it says it will sort them alphabetically - is there any way around that?

    • Sort like time attribute: If your calculated attribute represents dates, this option places your date values in the correct order.
      • Note: This option applies only to single attributes. Combining attributes, such as adding a year to the end of a month, will result in sorting alphabetically instead of chronologically.
    0
  • Dane
    Zendesk Engineering
    Hi John,
     
    Can you share your calculated attribute for me to determine what can be done?
    0
  • Ulises Soto

    Hi, 

    I am trying to obtain only the last timestamp of when the ticket was updated with a public comment by the end-user and from the agent. 

    I am running into the following 2 issues:

    1. I am getting all the timestamps of when the ticket was updated with a public comment 
    2. Both columns display the information without overlapping in the same row (if that makes sense?) 

    These are the queries I have:

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi UIises,

    With those formula, the attributes will return the timestamps of all end user and agent comments. To get the most recent timestamps, you can try using the Latest date functions. You can find sample recipes here: Working with earliest and latest date functions.
    0
  • Oliver Jacob

    Dane your suggestion for grouping orgnazations worked like a champ.  I am now stuck on how to report on month and year combined in a calculated attribute.   In this article it says it will sort them alphabetically - is there any way around that?

    • Sort like time attribute: If your calculated attribute represents dates, this option places your date values in the correct order.
      • Note: This option applies only to single attributes. Combining attributes, such as adding a year to the end of a month, will result in sorting alphabetically instead of chronologically.

    With the below formula, you can concatenate year, month and day to date in Excel. 1. Select a blank cell to place the concatenated date, and enter formula =A2&"/"&B2&"/"&C2 into the formula bar, then press the Enter key

    1. Create a new power query from your data table.
    2. Add custom columns and enter the formulas for getting year and month.
    3. Optional: you can enter the steps directly into the advanced editor (shown at the end of this post).

    Select the cells you want to format. Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.

     

    0
  • Lea Baun

    I'm trying to make an attribute that sees the following. 
    Tickets solved and tickets created withtin the last 3 hours.  Any idea on how to make that work? My metrics are tickets solved and tickets created, but what would you put to make the attribute just look at the last 3 hours? 

    0
  • John DiGregorio

    Lea Baun - add a filter for Last Updated

    0
  • Walter

    Hi Lea Baun,

    Try this for Tickets Created within 3 hours:

    IF(DATE_DIFF(NOW(),[Ticket created - Date],"nb_of_hours") < 3) THEN [Ticket ID] ENDIF

    Try this for Tickets Solved within 3 hours:

    IF(DATE_DIFF(NOW(),[Ticket solved - Date],"nb_of_hours") < 3) THEN [Ticket ID] ENDIF

    0
  • Lea Baun

    Thanks Walter,
    We are getting there!
    It shows something, but not quite the data. It shows 15.000 tickets, which is not the right data. :/  Do you have any other ideas? 

    0

Please sign in to leave a comment.

Powered by Zendesk