Trying to create a report that shows me 'Tickets Not Updated in 7 Days'
回答済み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
-
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.
-
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
-
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?
-
SOLVED!
Hi Christopher - thank you so much for this - it seems to have done the trick! Perfect!
Cheers,
Tom
サインインしてコメントを残してください。
4 コメント