Explore recipe: Recording the time when a checkbox was checked

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 query

Use the following steps to create this report in Explore.

1. In Explore, click the query () icon.
2. In the Queries library, click New query.
3. On the Choose a dataset page, click Support > Ticket updates > Support: Ticket Updates, then click New query. Query builder opens.
   
4. Next, you'll create a standard calculated attribute that records changes to your custom checkbox. From the calculations menu (), click Standard calculated attribute.
5. 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]
   ENDIF

   On 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:
   
   

6. When you're finished, click Save.
7. Now you can construct the query using your new calculated attribute. In the Metrics panel, click Add.
8. From the list of metrics, choose Updates > Updates, then click Apply. Ensure that the metric aggregator is set to D_COUNT.
9. In the Rows panel, click Add.
10. From the list of attributes, choose Calculated attributes, then the name of the standard calculated attribute you created. When you are finished, click Apply.
11. 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.
12. 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.
    
    
13. In the Rows panel, click Add.
14. 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: