Recent searches


No recent searches

Explore recipe: Recording the time when a checkbox was checked



image avatar

Erin O'Callaghan

Zendesk Documentation Team

Edited Jun 21, 2024


3

28

28 comments

Does this formula work for any custom field? I guess the better question are timestamps saved for all custom fields?

 

Thanks

1


image avatar

Chandra Robrock

Most Helpful - 2021Community Moderator

Hi @... - While you won't see an attribute exclusive to the custom field change timestamp, you will be able to understand the exact timestamp in which a custom field was updated by using the Update - Timestamp. The timestamp shown within the Update - Timestamp attribute will be based on the exact data you're looking at in Explore. 

For instance, in the formula provided in this recipe article, the Update - Timestamp attribute will only reflect ticket updates in which Checkbox_1 was selected.

In terms of whether you'd be able to use this same formula for other custom fields, you should be able to use this for most (if not all) custom ticket field types. However, you'll likely need to change the formula based on the type of custom field you'd like to report on since not all custom field types would return an old/new field value of either 1 or 0. Instead, the custom field value might actually be text or a ticket tag.

Additionally, in the case of a dropdown menu that has a lot of different options, this formula might be a better way to go so that you don't have to explicitly call out each individual dropdown menu option within your query.

IF ([Changes - Field name]="Dropdown_1")
AND
([Changes - Previous value]=NULL))
AND
([Changes - New value]!=NULL)
THEN
[Update - Timestamp]
ENDIF

Hope that helps point you in the right direction!

0


@... thanks for that response! I've been searching for the appropriate replacement for the Insights recipe Duration between two or more ticket events and this seems to be a piece of it.

In the long run, I'm trying to report on the time between escalation and first resolution, and separately, the time to escalation.

Escalation happens via a dropdown named "Escalate" in our instance, but I can't find an example of using a custom field for the dropdown (e.g. what to put where you have "Dropdown_1"). I've tried "Escalate" (the name of the field), "[Escalate]", and "custom_field_#####" (with the field's ID replacing #s) and they're all coming up empty. It could be that I've messed something else up, but could you verify which syntax is correct?

Thank you!

0


image avatar

Chandra Robrock

Most Helpful - 2021Community Moderator

@... I just double checked in my own Zendesk instance and can confirm that you'll want to use: [Changes - Field name]="Escalate"

If by chance you're using the exact formula I provided above, it appears I had accidentally left an extra parenthesis that isn't needed on Line 3, which would also cause a syntax error in Explore.

It should be:

IF ([Changes - Field name]="Dropdown_1")
AND
([Changes - Previous value]=NULL)
AND
([Changes - New value]!=NULL)
THEN
[Update - Timestamp]
ENDIF

Apologies if that might've been the thing tripping you up!

0


Thanks @...!

0


Thanks, Chandra! I had that right, and I caught those errant parenthesis, so it must be something else causing this particular query to come up empty, because I've built two others with this recipe and new custom fields and they're populating just fine.

For those interested: we're trying to track new custom periods of a ticket's lifecycle, like "Time to Escalation" and "From First Escalated Response to First Resolution". I created triggers that check custom fields at the start of those periods, and triggers that uncheck at the end, and am now using this Explore recipe to report on those new KPIs!

0


I'm trying to do this in my query, but not having any luck. The field I am using is a drop down, so I'm not sure if I should be using something else aside from NULL. 

0


image avatar

Chandra Robrock

Most Helpful - 2021Community Moderator

@... NULL should be correct, even for dropdown fields.

I'm thinking 1, 5 and 10 might be the actual dropdown field values rather than the underlying ticket tag associated with those dropdown field values. You'll want to use the actual tag as the [Changes - New value] value instead.

For example, if the underlying ticket tag for those dropdown values are nps_1, nps_5, and nps_10, the end result would look like this:

IF ([Changes - Field name]="Nicereply CSAT")
AND ([Changes - Previous value]=NULL)
AND (([Changes - New value]="nps_1")
OR ([Changes - New value]="nps_5")
OR ([Changes - New value]="nps_10" ))
THEN
[Update - Timestamp]
ENDIF

1


This query is great for a singular field. However, if I wanted to apply this attribute for multiple checkbox fields in the same query it won't let me unless I include all the null values. Is there a way where I can accomplish this?

0


HI Brendan,
 
Can you elaborate on what you mean when you say "apply this attribute for multiple checkbox fields in the same query"? Are you creating multiple attributes using this formula, or creating a single attribute with code that includes several checkbox fields (and if so, can you show us your code for that)? Thanks!

0


Is there a way to get this calculated attribute to work like a "real" timestamp, for filtering? It seems like it's not possible to have a "past 7 days" type time filter based on this attribute. 

1


image avatar

Gab Guinto

Zendesk Customer Care

Hi Collin,
 
The option to filter by advanced ranges is not possible within custom attributes, but since your custom date attribute is based on Update - Timestamp, you should be able to use the native Update - Timestamp attribute to set an advanced date range filter for your query.

0


HI Folks,

I have a numeric field and i want to find the timestamps for data entered into that numberic field. Is it possible? If yes, please guide me.

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Shashank,
 
Yes, you should be able to get the timestamps of updates made to numeric fields. Similar to the formula discussed above, just reference the name of the custom numeric field via [Changes - Field name]. Example:
IF [Changes - Field name]="My numeric field"
AND [Changes - Previous value]=NULL
THEN [Update - Timestamp]
ENDIF
This attribute formula should show the timestamps when a value was entered into the custom numeric field.

1


Thank you Gab. It works.

0


I am trying to build something that will show me the duration between events. Specifically, when a custom checkbox is checked, I need the time from ticket creation until the checkbox was selected. I have not seen this recipe yet and am hoping someone has the answer. Thanks in advance. 

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Richie,
 
You can create another custom metric to calculate the duration between Ticket created - Timestamp and the custom checkbox timestamp using the function DATE_DIFF (Explore functions reference). 

-1


Hello,

First of all, thank you very much for this! I was able to use it to provide very valuable information to my colleagues. I have one more question though:

Some tickets now have 2 entries, one where the checkbox was checked on the 16th of August for example and one for the 22th of August. Is there a way so I can only show the most recent one? We are only interested when it was LAST updated and now we have a ticket list that has a few more entries in comparison to the exact amount of tickets that should appear in the list.

Thank you!

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Toon,

You can try using the latest date functions to pull up the most recent update made on the checkbox. You can refer to this article: Working with earliest and latest date functions.

0


Hi Gab,

Thank you for this. I'm almost getting there. It is probably my coding going wrong, but by using the DATE_LAST I do receive the latest update date, but I can no longer FORMAT the date.

This is how it is now which gives me indeed the information I want:
IF ([Changes - Field name]="Flowset Launched")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
DATE_LAST([Update - Date])
ENDIF

My previous code however made sure it was in a format we use in Belgium:
IF ([Changes - Field name]="Flowset Launched")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
DATE_FORMAT([Update - Date],"DD-MM-YYYY")
ENDIF

Any idea how I can combine the two? I tried creating a new Calculated Attribute:
DATE_FORMAT([When Flowset Launched],"DD-MM-YYYY")

But in the end this gives me an error message that I should contact support :)

Kind regards and thx for the help!

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Toon,

You can change the date format through Chart configuration → Date display format.

0


Hello Gab,

Again, thank you for your time. This is strange. I don't have that option anywhere?

Would make other calculated attributes I created unnessecary. Is that a custom option somehow?

Kind regards

0


image avatar

Gab Guinto

Zendesk Customer Care

Hi Toon,

This should be under Chart configuration, from the menus on the right side pane.

0


Hello Gabe,

Alright! I found it after some searching.

What I did wrong was that in not knowing I could use that option to change the date format, I made calculated attributes to change the date format manually, but I didn't select the "Sort like time attribute".

This caused Explore not to recognize my attributes as dates and that was the reason the "Date display" option wasn't showing up in my chart config.

Now everyting is working perfectly. Thank you!

0


Trying this recipe and having trouble getting any results. Here is the formula I am using:

IF ([Changes - Field name]="{Escalated}")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
[Update - Timestamp]
ENDIF

Here is a screenshot of the report yielding no values:

Any pointers on troubleshooting this?

I tried changing ([Changes - New value]="1") to ([Changes - New value]="true") thinking that would work, but it didn't. 

Thanks

2


I have a custom ticket status in use.  Is it possible to create a timestamp for this custom status and then report on it?Ultimately, I am looking for something like [CustomStatus - Timestamp] - [Ticket Created - Timestamp] to get a duration value.  For one of my SLA reports, if the time between creation and this status is < 5 minutes, the ticket does not need to be included.  

0


image avatar

James G

Zendesk Customer Care

Hello Erik,
 
Yes this is possible! To achieve this, you simply need to create a custom attribute that captures the specific timestamp, similar to the example provided in this article. Once you have set up the custom attribute, you can then create a custom metric using the DATE_DIFF function to calculate the duration.

1


Matthew Natali 
Replace 
     IF ([Changes - Field name]="{Escalated}")
With
IF ([Changes - Field name]="Escalated")

No braces, { or }, around the field name Escalated.

0


Please sign in to leave a comment.