Recent searches


No recent searches

DATE_DIFF() Between 2 Custom Attributes

Not Planned


Posted Oct 20, 2021

I recently tried to create a custom attribute that used the DATE_DIFF() function to get the time between 2 custom timestamp attributes. 

I have been advised by a Zendesk Support agent that it is not currently possible to create a custom DATE_DIFF() attribute/ metric that will get the time between 2 custom timestamp attributes.

I feel that this is a huge oversight as in reporting I need to compare 2 events that are not native Zendesk attributes, i.e. the time between a group assignment and the time a checkbox was filled. This feature was available in Insights can this please be made available in Explore


19

23

23 comments

This was possible to create in Insights, can the ETA please be expedited for the current bug so this is achievable in Explore.

4


I have a similar request, with a small difference, I need to figure the diff between when a custom field is updated and when the ticket was created, in business hours.

0


derrek jennings - This is possible, you need to create a standard calculated attribute.

This is for minutes - ROUND(DATE_DIFF([ATTRIBUTE],[Ticket created - Timestamp],"nb_of_minutes"))

This is for hours - ROUND(DATE_DIFF([ATTRIBUTE],[Ticket created - Timestamp],"nb_of_hours"))

The attribute is the custom ticket field you want to want to report against

 

 

 

1


Great, thank you very much Kerry Charlton! I had a modified formula based on some google searches, but was not as advanced as what you provided.

Your suggestion is returning results, thank you.

I tried changing the "nb_of_hours" to be "Business hours (min)/60". Is nb_of_hours straight hours? I thought I read that it was, so thought I'd try business hours, but I am getting no results. Not sure if that would be related at all to the issue I'm waiting on ZD support to find out why the timestamp on our custom date field always has the time portions as 0.

 

Thanks again Kerry, very much appreciated!

0


derrek jennings to get the timestamp when the ticket field was updated you would have to create a custom attribute using a formula similar to this, created in the Ticket Updates dataset:

IF ([Changes - Field name]="CHECKBOX NAME")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
[Update - Timestamp]
ENDIF

If the ticket field is not a checkbox you could use a formula similar to this, once again this would have to be created in the Ticket Updates dataset.

IF ([Changes - Field name]="FIELD NAME")
AND
([Changes - Previous value]=NULL)
AND
([Changes - New value]!=NULL)
THEN
[Update - Timestamp]
ENDIF

 

Once you have the ticket field updated timestamp you will need to follow Kerry Charlton's formula to get the date difference between the newly created attribute and the created date timestamp.

When extracting the date difference between your ticket field being updated and the ticket being created 'nb_of_hours' would be the correct date_part to use. 

To my knowledge it is not possible to return this date difference in business hours, but you may want to reach out to Zendesk directly to confirm that. 

You can read more about DATE_DIFF here

0


It seems that the title of this article is spot on - I cannot find a way to calculate the difference between two custom attribute timestamps. The solutions proposed here explain how to get the date difference between one custom attribute timestamp and one standard field timestamp

For example, I'm doing the following:

1. Create two custom attributes, following the format of the above comment. I named these "Tech Support Initial Response" and "Tech Support Escalation Time"
2. Create another attribute following the date formula that Kerry shared above. This can work to compare the date between one of these attributes and one default field timestamp. It does not work to compare the time between the two custom attributes.

Note that both of these options work for calculating the time between one of the custom timestamps, and a default field timestamp (creation date):

This works:
DATE_DIFF([Tech Support Escalation Time],[Ticket created - Timestamp], "nb_of_hours")
and this works:
DATE_DIFF([Tech Support Initial Response],[Ticket created - Timestamp], "nb_of_hours")

However, it's not possible to get the time between the two custom timestamps. This returns nothing:

DATE_DIFF([Tech Support Initial Response],[Tech Support Escalation Time], "nb_of_hours")

5


Also really needing this -- seems like it should be standard.

2


Another vote here, I've got a huge backlog of requested reports that require this and I have to tell people that the simple calculations they are requesting aren't possible because the product can't do it. 

2


Somehow what's possible is to use the Result Metrics Calculation to make a calculation.

I've done one calculated metric that gives me the DATE_DIFF between ticket creation and my first timestamp. Same with the second timestamp. And then a result metrics calculation of metric2 - metric1.

Those calculated metrics need to be in the "metrics" for the result metrics calculation to work, but you can check the box at the bottom "Clear used metrics" to hide these 2 metrics from your table.

0


We are also experiencing this issue and have heard from our team that they are frustrated we cannot get what seems to them like an easy-to-achieve metric.

1


I would like to vote this feedback as well! It would be helpful to calculate the difference between 2 custom attribute timestamp. 

1


It would be very cool to have the function described in this post, it would give users access to the most detailed and flexible reporting I think it is worth attention!

Perhaps this is included in the roadmap and is there a timeline for implementation?

1


It is very frustrating that this can't be done. Not having this is hindering the ability to get critical data to our teams to make informed decisions around many aspects of operations.

0


Is it possible to use the DATE_DIFF function using Today() or Now() as one of the date/time fields for comparison with a standard Zendesk field ie. [Ticket created - Timestamp]?

When I create the calculated metric ZD Explore accepts the syntax but later displays an err when running the report.

Example:
DATE_DIFF(NOW(),[Ticket created - Timestamp],"no_of_hrs" )

Err:
Failure to calculate SUM(Aging (hrs)) because the following values can't be evaluated: Ticket created - Timestamp.

1


Fergus McCandless Are you creating this as a metric? I have created it as an attribute without getting any errors 

DATE_DIFF(NOW(),[Ticket created - Timestamp],"nb_of_hours")

0


Hi Joanna Kime - Thanks for your reply.  I've created it as a metric and now also as a calculated attribute but both don't generate any data and give the warning/err mentioned above (and below).

Failure to calculate SUM(Aging (hrs)) because the following values can't be evaluated: Ticket created - Timestamp.


0


Hello Fergus McCandless

It looks like there is a difference in your formula & that is probably the issue - 

DATE_DIFF(NOW(),[Ticket created - Timestamp],"nb_of_hours")

0


Thanks Londa Cabrera you have an eagle eye!  :-). Unfortunately, I made a typo above (and not in the calculated metric definition).  The problem is still existing.

In parallel, Fabio from Zendesk Support connected with me and offered a workaround using "Liquid Markup" which I'll try.

0


image avatar

Eugene Orman

Zendesk Product Manager

Thank you for all your great feedback. The ability to calculate the time duration between two custom events that occurred during the lifecycle of the ticket can be very insightful.  Unfortunately, the current data model of the Updates history dataset and calculations functionality can not provide this functionality. 

While we would love to address this issue, we cannot work on it this year. 

-2


Hello Zendesk, Is there any update on when this will be available in Explore? I too have the need to get the difference between 2 custom attributes. 

1


+1

0


+1

 

Calculating two custom attributes will help me get the time difference between when the survey was sent and when it was rated.

0


+1

 

My instance is integrated with JIRA, and I’ve created custom attributes that return a timestamp when a custom field based on JIRA status is updated. To calculate the duration from the Open to Solve status in JIRA, I would need to use the date_diff formula between the two custom attributes timestamp

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post