Explore functions reference

Have more questions? Submit a request

29 Comments

  • Johann Lazarus
    Comment actions Permalink

    How do I represent the following timestamp 2018-12-10T06:07:23 as 10/12/2018 17:07? 

    I've been able to use the custom 'Date display format' to format the date but the time segment isn't particularly clear to me. I presume that Zendesk is saving the timestamp in a different timezone? (I'm based in Melbourne Australia)

    0
  • Patrick Bosmans
    Comment actions Permalink

    Hello Johann,

    When building a query, the timestamp will be in UTC time.  Adding the query to a dashboard with allow the data to be referenced via the timezone of the user currently viewing the dashboard.

    0
  • Greer Davis
    Comment actions Permalink

    Putting it on the dashboard doesn't help since I need to send this information to a client who doesn't have access. Is there any way to get this formatted so I don't have to do extra work in Excel?

    0
  • Celia Johnson
    Comment actions Permalink

    "You can calculate the difference between two days, years, quarters, half years, hours, minutes, or seconds using the DATE_DIFF functions"

    Does this mean that weeks and months are specifically excluded? 

    0
  • Patrick Bosmans
    Comment actions Permalink

    Greer,

    At this time these are the only formatting options we have in Explore.  Our team is working on releasing new scheduling updates to individual users later this year.

     

    Celia,

    I believe Jessica just missed explicitly stating them, but you should be able to use weeks and months as well.

    0
  • Patrick Bosmans
    Comment actions Permalink

    Celia, 

    My apologies, but it looks as though week is not possible.  This is a screenshot from the function definition inside of Explore.

    0
  • Celia Johnson
    Comment actions Permalink

    Well, nobody will reply to my support ticket, so I guess I'll try again here. This article still appears to be impossible. "This section lists the available date functions. In the Functions window, you can view only date functions by clicking the Filter button, then typing Date."  As far as I can tell,  there's no filter button in the functions window, and if you do field instead of filter, you don't have any of the options in this article. 

    0
  • Nicole - Community Manager
    Comment actions Permalink

    Hi Celia -

    I checked on your ticket and have asked that someone get back to you.

    0
  • Rob Stack
    Comment actions Permalink

    Hi Celia, I'm sorry you had difficulties with the article. I agree that the filters could have been explained better and I've made some changes that hopefully help with that. I hope this helps!

    1
  • Justin
    Comment actions Permalink

    Is it possible in Explore to measure the time in hours that a ticket had a specific value applied within a ticket field? For example, let's say we have a drop-down field with Options 1, 2, and 3. Can we build a query that shows us how many hours a ticket had Option 1 applied before an agent updated it to Option 2?

    0
  • Traian Vila
    Comment actions Permalink

    Hello,

    Is there a _date_part that would return the number of business days from the configured schedule instead of all calendar days returned by nb_of_days ?

    Thanks,

    Traian

    0
  • Shaun Amyotte
    Comment actions Permalink

    Hey folks,

     

    Playing around trying to create a query that will eventually show me the break down of time spent in each state and playing with Date_diff I noticed it seems the newest date needs to be first, but the article says the opposite.

    How I tested:

    Filter: Tickets created last week

    custom metric:

    DATE_DIFF([Ticket Created - Date],TODAY(),"day")

    Metric:

    Display MAX of that metric

    Columns

    Ticket ID

    Result

    A column graph that shows negative numbers

    Flipping to

    Columns -> By Ticket ID  

    DATE_DIFF(TODAY(),[Ticket Created - Date],"day")

    shows positive numbers

    2
  • Molly
    Comment actions Permalink

    Hi @Traian! Unfortunately there isn't a way to do this at this time.

     

    @Shaun - great catch! I validated this with the support team and you are correct. I'm going to flag this article for updates. As an aside, I like using "nb_of_days" as "day" here will just return days in the month:

    I'm going to suggest that update as well. Cheers!

    0
  • Brian Larson
    Comment actions Permalink

    Hi - I have a custom "Due Date" field. Is there a way to filter by tickets that are past the Due Date set on the ticket? Showing over due tickets?

    Thanks,
    Brian

    0
  • Adrian Joseph Magboo
    Comment actions Permalink

    Hi Brian!

    You can create a View that can filter your tickets using your custom Ticket field (Due Date). You can learn more about it https://support.zendesk.com/hc/en-us/articles/218032348-Understanding-custom-ticket-fields-in-business-rules-and-views#topic_znk_mnk_xj

    0
  • Makmur Arafat
    Comment actions Permalink

    Hi, I have a problem to create calculation based on my metrics. Looks like i can't divide using basic operators.

     

    I want to divide a value in a cell with the sum of all data, which I use a metrics before to count a value in each cell.

    Ex : I have a metrics A with if function inside, and I want to divide with summary of all value from metrics A, but I dont get any result

     

    Thank you

    0
  • Graeme Carmichael
    Comment actions Permalink

    Makmur

    It sounds like you are calculating a percentage based on your original metric?

    If you want to display both the original metric and the percentage value on your table, you need to make a copy of your metric. Then use Result Configuration>Result Path Calculation and set one of you metrics to show:

    to give:

    0
  • Makmur Arafat
    Comment actions Permalink

    Nice! Thank you Graeme

    0
  • Makmur Arafat
    Comment actions Permalink

    Hi Graeme, I think I have another problem here.

     

    I want to divide two of my own metrics because I want to get a result from this kind of formula : a/(b/c)

    while a is a metric for each assignee name and b is sum of a and c is total of assignee name

    I already have 2 metrics, lets call it metric 1 for a and metric 2 for (b/c)

     

    Heres the calculation for metric 1 :

    IF ([Comment present]) AND ([Update ticket status - Unsorted]="Solved") THEN [Ticket ID] ENDIF

     

    And for metric 2 :

    D_COUNT(metric 1)/c        while c is reffering as above

     

    And when I want to divide metric 1 with metric 2 as

    VALUE(metric 1)/VALUE(metric 2)

     

    and it shown nothing in result.

     

    Thank you.

    0
  • Graeme Carmichael
    Comment actions Permalink

    Makmur

    If you have created custom metrics and need to perform further calculations using these metrics, go to Result Manipulation>Result Metric Calculation and build your new formula there.

    0
  • Makmur Arafat
    Comment actions Permalink

    Very nice! Thank you so much Graeme!

    0
  • Wolf Hilbl
    Comment actions Permalink

    Hi there,

    is there a way to always round up? Like the actual EXCEL function ROUNDUP? 

    Kind Regards Wolf

    0
  • Graeme Carmichael
    Comment actions Permalink

    Wolf

    If you use the ROUND function like this, you should get the same as ROUNDUP

    • ROUND(VALUE+0.49)

     

    1
  • Wolf Hilbl
    Comment actions Permalink

    Thank you Graeme, that would have worked too.

    I have found the command, it is CEIL(VALUE), like ceiling from SQL.

    Kind regards

    Wolf

    1
  • Graeme Carmichael
    Comment actions Permalink

    Wolf

    Yes that is much better, Thank you for sharing.

    0
  • Andrei Kamarouski
    Comment actions Permalink

    Hi, 
    How do I get previous month filter in metric using Explore formulas? Is there something similar to THIS-1 for Month (Solved) in Insights? 

    0
  • Graeme Carmichael
    Comment actions Permalink

    Andrei

    There is a pre-built metric that may help you.

    In the Support Tickets dataset, look for the Tickets solved-Last month metric. This metric is built by Zendesk, but you can build your own 'Date range calculated metrics'.

    0
  • Andrei Kamarouski
    Comment actions Permalink

    Hi Graeme, 
    Thx for the information! This metric allows me to use OUNT or D_COUNT only, but I need to use SUM for a Numeric field calculation. How do I get this? 

    0
  • Graeme Carmichael
    Comment actions Permalink

    Andrei

    Go to Calculations>Date Range Calculated Metric to define your metric.

    By default all the aggregators will be available.

     

    0

Please sign in to leave a comment.

Powered by Zendesk