In this Explore recipe, you'll learn how to create a report that captures the time when a custom checkbox was checked. If the checkbox is ever unchecked, the first timestamp of when it was checked will still be captured. However, if the checkbox is later rechecked, there will be a secondary entry for when the checkbox was checked.
In this example, a checkbox was checked, unchecked, and then rechecked. Two timestamp entries are recorded:
What you'll need
Skill level: Easy
Time required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- A checkbox custom field. For help creating this field type, see Adding custom fields to your tickets and support request form
Creating the report
Use the following steps to create this report in Explore.
- In Explore, click the reports (
) icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Updates history, then click Start report. The report builder opens.
- Next, you'll create a standard calculated attribute that records changes to your custom checkbox. From the calculations menu (
), click Standard calculated attribute.
-
On the Standard calculated attribute page, enter a name for your attribute like Checkbox checked, then enter or paste the following formula:
IF ([Changes - Field name]="{your_checkbox_field_name}")
AND
(([Changes - Previous value]="0") OR
([Changes - Previous value]=NULL))
AND
([Changes - New value]="1")
THEN
[Update - Timestamp]
ENDIFOn the first line of the formula, replace "{your_checkbox_field_name}" with the name of your custom checkbox in quotes. For example, if your custom checkbox is named "Checkbox_1" replace "{your_checkbox_field_name}" with "Checkbox_1".
You'll end up with an attribute that looks like this: - When you're finished, click Save.
- Now you can construct the report using your new calculated attribute. In the Metrics panel, click Add.
- From the list of metrics, choose Updates > Updates, then click Apply. Ensure that the metric aggregator is set to D_COUNT.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes, then the name of the standard calculated attribute you created. When you are finished, click Apply.
- Explore now displays your report, however NULL values (where there is no timestamp) are displayed. Next, you'll filter these NULL values from the report. Click the calculated attribute you just added to the Rows panel.
- On the attribute settings page, click the Excluded tab, enable the NULL checkbox, then click Apply. You'll see that NULL values are excluded from the report.
- In the Rows panel, click Add.
- From the list of attributes, choose Ticket > Ticket ID, then click Apply. This adds the ticket ID and exact timestamp for when the checkbox was checked to the report.
Here's an example of how the finished report might look:
28 comments
Jed Hollander
Does this formula work for any custom field? I guess the better question are timestamps saved for all custom fields?
Thanks
1
Chandra Robrock
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
Cameron Dunn
@... 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
Chandra Robrock
@... 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
Dave Dyson
Thanks @...!
0
Cameron Dunn
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
Charlie Smith
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
Chandra Robrock
@... 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
Brendan Crowley
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
Dave Dyson
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
Collin C
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
Gab Guinto
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
Shashank Agrawal
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
Gab Guinto
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:This attribute formula should show the timestamps when a value was entered into the custom numeric field.
1
Shashank Agrawal
Thank you Gab. It works.
0
Richie Ferguson
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
Gab Guinto
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
Toon de Jonghe
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
Gab Guinto
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
Toon de Jonghe
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
Gab Guinto
You can change the date format through Chart configuration → Date display format.
0
Toon de Jonghe
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
Gab Guinto
This should be under Chart configuration, from the menus on the right side pane.
0
Toon de Jonghe
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
Matthew Natali
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:
![](/hc/user_images/8GsaZIAcjEoig8VlR1YU-w.png)
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
Erik Cerbulis
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
James G
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
Sami
Matthew Natali
Replace
IF ([Changes - Field name]="{Escalated}")
With
IF ([Changes - Field name]="Escalated")
No braces, { or }, around the field name Escalated.
0