Recent searches


No recent searches

Trying to create a report that shows me 'Tickets Not Updated in 7 Days'

Answered


Posted Apr 24, 2023

I'm trying to create a calculated metric for a report that will show me % of tickets that haven't been updated in 7 days. 

I've tried a few queries

COUNT(FILTER([Ticket ID], DATE_DIFF(TODAY(), [Ticket solved - Date]) > 7 AND [Ticket status - Status] != "Closed" AND [Ticket status - Status] != "Solved"))

and this

COUNT(IF (DATE_DIFF(TODAY(), [Ticket solved - Date]) > 7 AND [Ticket status - Status] != "Closed" AND [Ticket status - Status] != "Solved", [Ticket ID], NULL))

and this 

IF (DATE_DIFF(TODAY(), [Ticket solved - Date]) > 7 AND [Ticket status - Status] != "Closed" AND [Ticket status - Status] != "Solved") THEN 1 ELSE 0 ENDIF

Call it something like 'not-updated-tickets' 

I was then going to create another metric Tickets - [Ticket ID]

and finally Result Calculation Metric

(not-updated-ticket / COUNT(Tickets)) * 100

But I keep getting a syntax error already on the first SQL - if anyone has a working example of the report I'm looking for - I'd be happy if you could share. 

Thanks,

T

 


0

5

5 comments

image avatar

Christopher Stock

Zendesk LuminaryCommunity Moderator

Hi Thomas Koch, is it specifically 'Solved' tickets that haven't been updated that you are interested in? Your queries all use the 'Ticket solved - Date' attribute.

One approach you could try is using the new DATE_LAST function to create an attribute that gives the timestamp of the latest update to a ticket, then filter to only show tickets where that timestamp is more than 7 days ago.

0


Hi Christopher,

oh good point - my quieres should use 'Ticket updated - Date' -  and that should be >7 days and then only look for tickets that are not "!=", solved or closed. 

I tried 

COUNT(FILTER([Ticket ID], DATE_DIFF(TODAY(), [Ticket updated - Date] - Date]) > 7 AND [Ticket status - Status] != "Closed" AND [Ticket status - Status] != "Solved"))

unfortunately I get the same message - :( 

Cheers,

Tom

0


image avatar

Christopher Stock

Zendesk LuminaryCommunity Moderator

Hey Thomas, I've had another look at this and managed to get something working.

Using the 'Support - Tickets' dataset, this is the custom metric I created:

IF ([Ticket status - Unsorted] != "Solved" AND [Ticket status - Unsorted] != "Closed") AND DATE_DIFF(NOW(), [Ticket updated - Timestamp], "nb_of_days") > 7 THEN [Ticket ID] ENDIF

I've called it 'Unsolved tickets - not updated in last 7 days'.

You can then add this metric to a report alongside the standard 'Unsolved tickets' metric and create a 'Result metric calculation':

COUNT(Unsolved tickets - not updated in last 7 days)/COUNT(Unsolved tickets)

To give you the percentage.

Is that what you're after?

1


SOLVED!

Hi Christopher - thank you so much for this - it seems to have done the trick! Perfect! 

Cheers,

Tom

0


Awesome Metric, many thanks from 2025!

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post