Recent searches


No recent searches

Working with earliest and latest date functions



image avatar

Eugene Orman

Zendesk Product Manager

Edited Jun 21, 2024


5

41

41 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


Is it possibly to use DATE_FIRST to report on the first date an organization created a ticket?

Our use-case is that we want to understand ticket volumes for newly launched customers. I cannot go off of the date that the organization was added to ZenDesk, because we historically have added organizations when they sign contracts... it can be years before they actually launch.

So, the metric I need is 'first time existing organization submitted a ticket', set it to filter on dates within the last let's say 3 months, and then I would pull in ticket volumes by week for the organizations that fit the calculated metric.

Let me know if I am on the right track here or if this is something impossible.. or possible via another route. Thanks!

0


I have been trying to build a report where it returns the timestamp that a ticket was first set to pending or on-hold but something isn't right as I am getting null values for some tickets that did have that change happen. The numbers that do get returned appear accurate. This may or may not include other changes in the same update, which I don't care about checking.

My calculated attribute is Update to Pending/On-Hold.

IF ([Changes - Field name]="status"    
       AND ([Changes - Previous value]="open" OR [Changes - Previous value]="new")
        AND ([Changes - New value]="pending" OR [Changes - New value]="on-hold" OR [Changes - New value]="solved")
       AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name],[Changes - Previous value],[Changes - New value])=[Update - Timestamp])
THEN [Update - Timestamp]
ENDIF

Then I am plugging that into a calculated metric:

DATE_DIFF([Update to Pending/On-Hold],[Ticket created - Timestamp],"nb_of_minutes")

I suspect there is something off about the DATE_FIRST_FIX and have tried changing the values there but most combinations lead to nothing at all being returned. Is something causing it to be unable to detect the first change in some cases?

1


I'm trying to accomplish the same as Jordan and keep getting blank results for tickets that were changed to status "Pending". 

0


Hi Eugene Orman

I followed the steps to show the last agent update, and just like Colin Hutzan, I ran into an issue where there were a lot of Null or blank values. I checked some of those tickets and confirmed that agents had replied to all the tickets that I checked.

I am wondering if any of the following points have some kind of impact?

  • Custom Roles (since I can only see agent admin, and end-user and not any custom roles I created)
  • Downgraded Agents (now end-users or suspended)
  •  

0


can someone help me how to formula:

"Ticket First Solved - Date" ?

 

I need to create an attribute like that, thank you

0


image avatar

Andrei Kamarouski

Community ModeratorThe Wise One - 2021

Hey @..., here you are 

IF ([Changes - Field name]="status"    
       AND [Changes - Previous value]!="solved"
      AND ([Changes - New value]="solved" OR [Changes - New value]="closed")  
      AND  DATE_FIRST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name], [Changes - New value])=[Update - Timestamp]) 
THEN [Update - Timestamp] 
ENDIF

0


hi Andrei Kamarouski thank you for the formula

-----------

but, is the formula is only for timestamp?

 

how if i wanted to use only date?

i want to get this value ==> 2023-10-19

not this ==> 2023-10-17T12:48:20

0


image avatar

Andrei Kamarouski

Community ModeratorThe Wise One - 2021

Then use this

THEN [Update - Date] 

instead of this

THEN [Update - Timestamp] 

0


image avatar

Francis Casino

Zendesk Customer Care

Hello Ariya,
 
I'd like to express my agreement with Andrei's input. If you have any additional questions or if there are further concerns that you'd like to discuss, please don't hesitate to reach out. We're here to assist and provide you with any additional information or support that you may require. Your feedback and inquiries are highly valued, and we're committed to ensuring that you receive the best assistance possible.
 
Thank you for your engagement, and we look forward to addressing any further queries you might have.

0


I need help in fixing this:

IF ([Changes - Field name]="assignee_id" AND [Changes - Previous value]=NULL 
AND [Changes - New value]!="0" AND [Changes - New value]!=NULL
AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name])=[Update - Timestamp])
THEN [Update ticket assignee]
ENDIF

I tried to use the suggestion from this page: https://support.zendesk.com/hc/en-us/articles/4408845631258-Explore-recipe-Finding-the-first-assignee-for-a-ticket, but there's an issue. Every time the system updates the ticket and the  “Assignee” becomes NULL, the next assignee is treated as the FIRST, so I added the Date_First_Fix.

The problem now is the NULL values. There are a lot of tickets going to the NULL values even if they were assigned to agents.

I need to count the number of tickets wherein the agent is the FIRST assignee regardless of how many agents handled the ticket.
 

0


Please sign in to leave a comment.