Recent searches
No recent searches
Trying to create a report that shows me 'Tickets Not Updated in 7 Days'
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 comments
Christopher Stock
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
Thomas Koch
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
Christopher Stock
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:
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':
To give you the percentage.
Is that what you're after?
1
Thomas Koch
SOLVED!
Hi Christopher - thank you so much for this - it seems to have done the trick! Perfect!
Cheers,
Tom
0
Aaron Miller
Awesome Metric, many thanks from 2025!
0