Working with earliest and latest date functions



image avatar

Eugene Orman

Zendesk Product Manager

Edited Mar 05, 2025


5

41

43 comments

Hello!

I am trying to pull a report of tickets that have gone the longest without a public comment by an internal agent.

I have added a custom standard calculated attribute for latest public comment:

IF ([Comment present]=TRUE 
AND [Comment public]=TRUE 
AND DATE_LAST_FIX([Update - Date],[Comment present],[Comment public],[Ticket ID])=[Update - Date]) 
THEN [Update - Date] ENDIF

And I have built a report in the updates history dataset using the metric D_COUNT Tickets Updated w/public comment + the rows:

Latest public comment (this is what I named the calculated attribute)

Ticket ID

Ticket organization name

Ticket subject

Updater name

Assignee name

 

Filters:

Updater role: Agent, Admin

Ticket status: On hold 

Comment public: exclude NULL and FALSE

 

The returned results seem to be correct (in that the rows containing a value in the latest public comment column have the correct value), but most of the rows are returning no values/NULL for the latest public comment column. Why would this be the case? What changes should I make for this report?

2


Hi Franck,

If you were able to create a custom attribute that pulls the timestamp of the last agent comment, then you may use the function date_diff to calculate the duration from the native attribute Ticket created - Timestamp to your custom timestamp attribute. But unfortunately, the calculation can only return results in calendar hours. 
 

0


Hello,

We are using Zendesk Enterprise for technical support.

We have around 25% of our tickets that are automaticaly closed after some rules because the customer never reply to us, also when we provided the solution to the customer (reply in pending status) it may takes few days to have a confirmation and solve the ticket.

 

With this I would like to have create a different resolution duration indicator based on the last public comment - a duration indicator based on last public comment minus creation date. 

Based on this article I managed to have for closed ticket the timestamp for the agent last public comment.

1) How can I retrieve the ticket creation or ticket Full resolution Time stamp?

2) Is there an easyest way to figure out the indicator I am looking for (duration indicator based on last public comment minus creation date) in hours?

3) Is it possible to have it in business hours?

Best regards

 

0


Hi Vamshi,

You can try modifying the sample formula discussed here by replacing Updater name with [Update ticket ID]. 
DATE_LAST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])
Then filter your report by Update role to only include 'Agent' and 'Admin'. With that attribute and the role filter, and your report sliced by Ticket ID, the table should show the timestamps of the most recent public reply by an agent on each ticket.

0


Hello Everyone, 

 How to display the last public comment made by the agent EVEN if there is other internal comment on the ticket after the public comment ?

0


Hello Everyone, 

 

    Can someone please help on my requirement. I just want to get the last updated comment date & time on reports. Above metric which was provided is not working for me. 

1


Hi Tendai, 
 
I think you might be able to adapt this recipe: Explore recipe: Recording the time when a checkbox was checked, but using the group change part of the formula from Explore recipe: Tracking ticket assigns across groups (so, creating a Standard Calculated Attribute using the Support - Updates History dataset, but using the group change logic to update the timestamp. 

0


Could l be provided with a formula that we can get timestamps on tickets when they are escalated from team to team when a ticket is handed off as well as the time an agent would have held the ticket?

0


Hey Ravindra,
 
If you're on the Enterprise plan you should be able to track who created a group through the audit log as mentioned here: Viewing the audit log for changes
 
I hope this helps!

 

0


Hello All,

 

Can someone please help me - I want to know the new ticket group created in last 3 months.

Thanks,

Ravindra Singh

0


Sign in to leave a comment.