Count updates on specific custom field

Hey Stephan, in the Ticket updates dataset, there are four attributes dealing with changes to fields.  You can use these to find updates where a specific field was changed.

Hi there, 

I'm facing a similar issue here. In Insights I had/have the following custom metric:

SELECT # Ticket Updates WHERE [Text Field] New Value = [Change Progress] Booked into sprint

I can't seem to find a similar variant to count the number of times this field was updated to that particular drop-down selection in Explore; there's a lot of advise on IF THEN ELSE, but that's not what I'm after. 

Are you able to point me in the right direction? I'm a bit lost!

Thanks, 

Emma

Emma

It is a different way of thinking in Explore.

In your query, go to Calculations>Standard Calculated Metric.

Create a new metric like this :

IF [Changes - Field name]= "Change Progress" AND
[Changes - New value] = "Booked into sprint"
THEN
[Update ID]
ENDIF

Ensure you tick the option 'Compute Separately' at the bottom of the page.

You will then D_COUNT this metric to find the number of updates to your custom field "Change Progress" with a new value "Booked into sprint"

Thanks Graeme - I've followed this and it does seem logical, however no data is returned at all, which isn't right. 

This is the only metric my reporting is reliant on, so I'll do some digging and hopefully it'll be something that I can work out. 

I have a similar issue. I have created a standard calculated metric in the same way described above:

If I apply this to the data of one individual day it returns a number, however, as soon as I include data of more then one day, no data is returned. I do however get a warning that the SQL query returns more than 50000 rows.

Does anyone have an idea why this is not working? As far as I can tell it uses the same logic as the Zendesk standard metrics (e.g., Tickets created):Is it possible that this has something to do with "ACD Anzahl Outboundcalls" being a "numeric custom field" and not a "ticket custom field"?

Hi Jessica, 

Thanks for reaching out! Can you try adding a filter for Changes - Field Name = "ACD Anzahl Outboundcalls" to see if the error persists? Although this may seem redundant since it's mentioned in your custom metric, it helps limit the data the custom metric will check when counting the number of updates. 

Let us know if that helps!

Hi @...,

Thanks for the tip. I had created a separate post about this and got the same advice. You are right, it seems redundant, that's why I didn't think of it.

With the added filter I can report on data from one month at the time before I hit the row limit. Still not great but at least I only have to run the query 12 times to get the information I need instead of 365 times as it was before ;)