Explore recipe: Reporting on the duration of fields

<div class="body">
<div class="article-banners">
<div id="docs-wmp-link"><a href="https://support.zendesk.com/hc/en-us/articles/5411234991258-plan" rel="noopener" target="_blank">What's my plan?</a></div>
<div class="article-banner" id="suite_pee">
<table>
<tbody>
<tr>
<td><strong>All Suites</strong></td>
<td>Professional, Enterprise, or Enterprise Plus</td>
</tr>
</tbody>
</table>
</div>
<div class="article-banner" id="support_with_explore_pe">
<table>
<tbody>
<tr>
<td><strong>Support with</strong></td>
<td>Explore Professional or Enterprise</td>
</tr>
</tbody>
</table>
</div>
</div>
<div class="p">In this Explore recipe, you'll discover how to measure how long some of your ticket fields were set to a certain value. This gives you the tools to answer some important business questions like:
<ul class="ul" id="Explore-recipe-fileld-changes__ul_lc5_dyb_l3b">
<li class="li">What's the average time a ticket sits in each status through its lifecycle?</li>
<li class="li">How long do tickets sit with each of my support groups?</li>
</ul>
</div>
<p class="p">The examples in this article use the <strong>U</strong><strong class="ph b">pdates history</strong> dataset which stores details of updates and events that happen in Zendesk Support.</p>
<p class="p">This article contains the following topics:</p>
<ul class="ul" id="Explore-recipe-fileld-changes__ul_nk2_mzb_l3b">
<li class="li"><a class="xref" href="#topic_s5l_hjk_1hb">What you'll need</a></li>
<li class="li"><a class="xref" href="#topic_mkq_rzb_l3b">Calculating the average time a ticket is in each status through its lifecycle</a></li>
<li class="li"><a class="xref" href="#topic_y1x_rzb_l3b">Calculating how long tickets sit with a support group</a></li>
<li class="li"><a class="xref" href="#topic_hxr_y4c_cjb">Calculating how long tickets sit with multiple support groups</a></li>
<li class="li"><a href="#h_01HGXE62C8EAS2TREQ4CAF0BZP">Calculating how long tickets sit with their last support group</a></li>
<li class="li"><a href="#h_01FAK1QE1JX3F4M0HGQB8MXEY6" target="_self">Calculating duration of time for custom ticket fields</a></li>
<li class="li"><a class="xref" href="#topic_bny_rzb_l3b">Next steps</a></li>
</ul>
</div>
<div aria-labelledby="ariaid-title2" class="topic nested1" id="topic_s5l_hjk_1hb">
<h2 class="title topictitle2" id="ariaid-title2">What you'll need</h2>
<div class="body">
<p class="p" dir="ltr"><strong>Skill level:</strong> Intermediate</p>
<p class="p" dir="ltr"><strong>Time required:</strong> 20 minutes</p>
<ul class="ul" id="topic_s5l_hjk_1hb__ul_kxt_vcl_4fb">
<li class="li" dir="ltr">Zendesk Explore Professional or Enterprise</li>
<li class="li" dir="ltr">Editor or Admin permissions (see <a class="xref" href="https://support.zendesk.com/hc/en-us/articles/4408836002970-Adding-users-to-Explore" rel="noopener" target="_blank">Giving users access to Explore</a>)</li>
<li class="li" dir="ltr">Ticket data in Zendesk Support</li>
</ul>
</div>
</div>
<div aria-labelledby="ariaid-title3" class="topic nested1" id="topic_mkq_rzb_l3b">
<h2 class="title topictitle2" id="ariaid-title3">Calculating the average time a ticket is in each status through its lifecycle</h2>
<div class="body">
<p class="p">In this report, you'll discover the average time your solved tickets spent in each ticket status throughout their lifecycle.</p>
<p class="p"><strong class="ph b">To create the report</strong></p>
<ol class="ol" id="topic_mkq_rzb_l3b__ol_ury_q1c_l3b">
<li class="li" dir="ltr">In Explore, click the reports icon (<img alt="" class="image" height="16" id="topic_mkq_rzb_l3b__image_oxt_vcl_4fb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_query_icon.png" width="16">) .</li>
<li class="li" dir="ltr">In the Reports library, click <strong class="ph b">New report</strong>.</li>
<li class="li" dir="ltr">On the <strong>Select </strong><strong class="ph b">a dataset</strong> page, click <strong>Support</strong> &gt; <strong>Support - Updates history</strong>, then click <strong class="ph b">Start report</strong>. The report builder opens.</li>
<li class="li">In the <strong class="ph b">Metrics</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of metrics, expand <strong class="ph b">Duration - Field changes (hrs)</strong>, and then choose the following metrics:
<ul class="ul" id="topic_mkq_rzb_l3b__ul_kq3_ydc_l3b">
<li class="li"><strong class="ph b">New status time (hrs)</strong></li>
<li class="li"><strong class="ph b">Open status time (hrs)</strong></li>
<li class="li"><strong class="ph b">Pending status time (hrs)</strong></li>
</ul>
<p class="p">After you add each one, click the added metric and changed its aggregator to <strong class="ph b">AVG</strong>. This will display the average of all returned values. When you are finished, click <strong>Apply</strong>.</p>
<div class="note tip">
<span class="tiptitle">Tip:</span> If you have enabled the On-hold ticket status, you can also add the metric <strong class="ph b">On-hold status time (hrs)</strong>.</div>
</li>
<li class="li">From the Visualization type (<img alt="" class="image" height="16" id="topic_mkq_rzb_l3b__image_ng4_jfc_l3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_visualization_type.png" width="16">) menu, choose <strong class="ph b">Table</strong>.</li>
</ol>
<p class="p">Explore displays the finished report.</p>
<p class="p"><img alt="" class="image img-screenshot" id="topic_mkq_rzb_l3b__image_hyw_tfc_l3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_ticket_average.png"></p>
</div>
</div>
<div aria-labelledby="ariaid-title4" class="topic nested1" id="topic_y1x_rzb_l3b">
<h2 class="title topictitle2" id="ariaid-title4">Calculating how long tickets sit with a support group</h2>
<div class="body">
<p class="p">In this report, you’ll create a calculated metric that shows how long your tickets spend in your support group named <strong class="ph b">Support Group 1</strong>. This metric can be used on its own or with any other default or calculated metrics. This can be challenging as group names are stored as an ID in the Explore <em class="ph i">Field changes</em> attributes, but you'll learn how to discover the ID of your groups.</p>
<p class="p">This report measures the time a ticket spent in a group before being moved to another group; it does not include the time a ticket spent in a group before it was solved.</p>
</div>
<div aria-labelledby="ariaid-title5" class="topic nested2" id="topic_t5z_clj_l3b">
<h3 class="title topictitle3" id="ariaid-title5">Discovering your group ID numbers</h3>
<div class="body">
<p class="p">Before you can create the metrics for the report, you need to find the group ID for the <strong class="ph b">Support Group 1</strong> group in Zendesk Support.</p>
<p class="p"><strong class="ph b">To discover your group IDs</strong></p>
<ul class="ul" id="topic_t5z_clj_l3b__ul_vh3_nd3_s3b">
<li class="li">In your web browser, visit the following URL: <strong class="ph b"><em class="ph i">subdomain</em>.zendesk.com/api/v2/groups.json</strong>
<p class="p">(replace <em class="ph i">subdomain</em> with the name of your Zendesk subdomain)</p>
</li>
</ul>
<p class="p">You'll see a page showing information about all of your groups, including the group ID in JSON format. You can make the results easier to read by installing a JSON Viewer extension in your browser (for example, with Chrome, search the Chrome web store for JSON viewer).</p>
</div>
</div>
<div aria-labelledby="ariaid-title6" class="topic nested2" id="topic_xvs_2lj_l3b">
<h3 class="title topictitle3" id="ariaid-title6">Creating the custom metric</h3>
<div class="body">
<p class="p">Now, you'll create a custom metric that displays the duration that a ticket was assigned to a group that you specify. Before you start, make sure you've noted down the group ID using the previous procedure.</p>
<p class="p"><strong class="ph b">To create the custom metric</strong></p>
<ol class="ol" id="topic_xvs_2lj_l3b__ol_k1v_fvt_m3b">
<li class="li" dir="ltr">In Explore, click the reports icon (<img alt="" class="image" height="16" id="topic_xvs_2lj_l3b__image_l1v_fvt_m3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_query_icon.png" width="16">) .</li>
<li class="li" dir="ltr">In the Reports library, click <strong class="ph b">New report</strong>.</li>
<li class="li" dir="ltr">On the <strong>Select </strong><strong class="ph b">a dataset</strong> page, click <strong>Support</strong> &gt; <strong>Support - Updates history</strong>, then click <strong class="ph b">Start report</strong>. The report builder opens.</li>
<li class="li">From the <strong class="ph b">Calculations</strong> menu (<img alt="" class="image" height="16" id="topic_xvs_2lj_l3b__image_vly_kwt_m3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_calculations.png" width="16">), choose <strong class="ph b">Standard calculated metric</strong>.</li>
<li class="li">On the <strong class="ph b">Standard calculated metric</strong> page, give the metric a name, for example <strong class="ph b">Time spent in group</strong>.</li>
<li class="li">In the <strong class="ph b">Formula</strong> field, copy and paste the following formula. Replace the text <em class="ph i">Group ID number</em> with the group ID number you noted previously.
<div class="p">
<pre class="pre codeblock"><code>IF ([Changes - Field name]="group_id") AND ([Changes - Previous value]="<em class="ph i">Group ID number</em>")
THEN VALUE(Field changes time (min))/60
ENDIF</code></pre>
</div>
<div class="note tip">
<span class="tiptitle">Tip:</span> If you're working in a language other than English, <a href="https://support.zendesk.com/hc/en-us/articles/4408820266906" target="_self">read this article</a> to help you enter Explore formulas in your language.</div>
<p class="p">Supported aggregators when you use this metric in a report are SUM, AVG, MIN, MAX, and MED</p>
<p class="p">You'll end up with a page that looks similar to this:</p>
<p class="p"><img alt="" class="image img-screenshot" id="topic_xvs_2lj_l3b__image_mpr_3yt_m3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_fc_recipe_1.png"></p>
</li>
<li class="li">When you are finished, click <strong class="ph b">Save</strong>. Leave the report open and continue to the next procedure.</li>
</ol>
<div class="p">
<div class="note tip">
<span class="tiptitle">Tip:</span> After you create custom metrics, you can change their display format on the <strong class="ph b">Standard calculated metrics</strong> page for each metric. To do so, locate the metric in the <strong class="ph b">Calculated metrics</strong> folder and click the pen icon next to it. On the <strong class="ph b">Standard calculated metrics</strong> page, click <strong class="ph b">Options</strong> &gt; <strong class="ph b">Edit display format </strong>and choose <strong class="ph b">Custom</strong>. For example, you could add the suffix "hours", or increase the number of decimal places.</div>
</div>
</div>
</div>
<div aria-labelledby="ariaid-title7" class="topic nested2" id="topic_vsd_vvt_m3b">
<h3 class="title topictitle3" id="ariaid-title7">Using the custom metric in a report</h3>
<div class="body">
<p class="p">Now, you'll create a report that uses the custom metric you just created to report on the amount of time tickets spent in the <strong class="ph b">Support 1 group</strong>. Perform the following steps in the report you created previously, or in a new report that uses the <strong class="ph b">Support: Updates history</strong> dataset.</p>
<p class="p"><strong class="ph b">To use the custom metric in a report</strong></p>
<ol class="ol" id="topic_vsd_vvt_m3b__ol_f2g_qzt_m3b">
<li class="li">In the <strong class="ph b">Metrics</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of metrics, choose <strong class="ph b">Calculated metrics </strong>&gt; <strong class="ph b">Time spent in group</strong>, then click <strong class="ph b">Apply</strong>. Explore displays the total time that tickets spent in the <strong class="ph b" style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Helvetica, Arial, sans-serif;">Support 1</strong><span style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Helvetica, Arial, sans-serif;"> group. Set the metric aggregator to MED, or any other supported value you want.</span>
</li>
<li class="li">In the <strong class="ph b">Columns</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of attributes, choose <strong class="ph b">Time - Ticket update</strong> &gt; <strong class="ph b">Update - Year</strong> and <strong class="ph b">Time - Ticket update</strong> &gt; <strong class="ph b">Update - Month</strong>.</li>
<li class="li">Set the visualization type (<img alt="" class="image" id="topic_vsd_vvt_m3b__image_dvz_r1p_s3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_visualization_type.png">) to <strong class="ph b">Column</strong>. Explore displays a chart showing how long each of your tickets spent in the <strong class="ph b" style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Helvetica, Arial, sans-serif;">Support 1 group</strong><span style="font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Helvetica, Arial, sans-serif;">.</span>
<p class="p"><img alt="" class="image img-screenshot" id="topic_vsd_vvt_m3b__image_agh_zz4_s3b" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_recipe_changes_22.png" width="850"></p>
</li>
<li class="li">When you are finished, click <strong class="ph b">Save</strong>.</li>
</ol>
</div>
</div>
</div>
<div aria-labelledby="ariaid-title8" class="topic nested1" id="topic_hxr_y4c_cjb">
<h2 class="title topictitle2" id="ariaid-title8">Calculating how long tickets sit with multiple support groups</h2>
<div class="body">
<p class="p">In this report, you’ll create a calculated attribute that shows previous groups the ticket was assigned to. You can then use this attribute with the pre-built <strong class="ph b">Field changes time</strong> metric to calculate how long the ticket was in each group.</p>
<p class="p">Before you start, you'll need the ID numbers of the groups you want to query. To get the ID numbers, see <a class="xref" href="#topic_t5z_clj_l3b">Discovering your group ID numbers</a> in this article.</p>
<div class="note note">
<span class="notetitle">Note:</span> This section doesn't capture the last group a ticket was assigned to before being solved. For that information, see <a href="#h_01HGXE62C8EAS2TREQ4CAF0BZP">Calculating how long tickets sit with their last support group</a>.</div>
</div>
<div aria-labelledby="ariaid-title9" class="topic nested2" id="topic_f1b_wpc_cjb">
<h3 class="title topictitle3" id="ariaid-title9">Creating the custom attribute</h3>
<div class="body">
<p class="p">First, you'll create a custom attribute that checks whether the ticket has been assigned to the groups you list. If it has, the group name is returned. Before you start, make sure you've noted down the group ID using the previous procedure.</p>
<p class="p"><strong class="ph b">To create the custom attribute</strong></p>
<ol class="ol" id="topic_f1b_wpc_cjb__ol_c3j_2qc_cjb">
<li class="li" dir="ltr">In Explore, click the reports icon (<img alt="" class="image" height="16" id="topic_f1b_wpc_cjb__image_d3j_2qc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_query_icon.png" width="16">) .</li>
<li class="li" dir="ltr">In the Reports library, click <strong class="ph b">New report</strong>.</li>
<li class="li" dir="ltr">On the <strong>Select</strong><strong class="ph b"> a dataset</strong> page, click <strong>Support</strong> &gt; <strong>Support - Updates history</strong>, then click <strong class="ph b">Start report</strong>. The report builder opens.</li>
<li class="li">From the <strong class="ph b">Calculations</strong> menu (<img alt="" class="image" height="16" id="topic_f1b_wpc_cjb__image_f3j_2qc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_calculations.png" width="16">), choose <strong class="ph b">Standard calculated attribute</strong>.</li>
<li class="li">On the <strong class="ph b">Standard calculated attribute</strong> page, give the metric a name, for example <strong class="ph b">Previous group</strong>.</li>
<li class="li">In the <strong class="ph b">Formula</strong> field, copy and paste the following formula. Replace the example group IDs with the group ID numbers you noted previously. Then, replace the example group names with the names that correspond to your group IDs.
<div class="p">
<pre class="pre codeblock"><code>IF ([Changes - Previous value]="24120932") THEN "Dev Team"
ELIF ([Changes - Previous value]="24935531") THEN "Sales"
ELIF ([Changes - Previous value]="24072451") THEN "Support"
ENDIF</code></pre>
</div>
<p class="p">If you want to check for more groups than this example, add a new ELIF line for each group.</p>
<p class="p">You'll end up with a page that looks similar to this:</p>
<p class="p"><img alt="" class="image img-screenshot" id="topic_f1b_wpc_cjb__image_ucd_yqc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/duration_recipe_5.png"></p>
</li>
<li class="li">When you are finished, click <strong class="ph b">Save</strong>.</li>
</ol>
</div>
</div>
<div aria-labelledby="ariaid-title10" class="topic nested2" id="topic_wg4_wpc_cjb">
<h3 class="title topictitle3" id="ariaid-title10">Using the custom attribute in a report</h3>
<div class="body">
<p class="p">Now, you'll create a report that uses the custom attribute you just created to report on the amount of time tickets spent in each group. Perform the following steps in a new report that uses the <strong class="ph b">Support:  Updates history</strong> dataset.</p>
<p class="p"><strong class="ph b">To create the report</strong></p>
<ol class="ol" id="topic_wg4_wpc_cjb__ol_drl_lrc_cjb">
<li class="li">In the <strong class="ph b">Metrics</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of metrics, choose <strong class="ph b">Duration - Field changes (hrs)</strong> &gt; <strong class="ph b">Field changes time (hrs)</strong>, then click <strong class="ph b">Apply</strong>.</li>
<li class="li">In the <strong>Metrics</strong> panel, click the metric you just added, and change its' aggregator to <strong class="ph b">AVG</strong>.</li>
<li class="li">In the <strong class="ph b">Columns</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of attributes, choose <strong class="ph b">Time - Ticket solved</strong> &gt;<strong class="ph b">Ticket solved</strong><strong class="ph b"> - Month</strong>, then click <strong class="ph b">Apply</strong>.</li>
<li class="li">In the <strong class="ph b">Rows</strong> panel, click <strong class="ph b">Add</strong>.</li>
<li class="li">From the list of attributes, choose <strong class="ph b">Calculated attributes</strong> &gt; <strong class="ph b">Previous group</strong>.</li>
<li class="li">From the Visualization type menu (<img alt="" class="image" height="16" id="topic_wg4_wpc_cjb__image_rjq_lsc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_visualization_type.png" width="16">), choose <strong class="ph b">Column</strong>.</li>
<li class="li">From the Chart configuration menu (<img alt="" class="image" height="16" id="topic_wg4_wpc_cjb__image_dkw_vsc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/Explore_chart_configuration.png" width="16">), choose <strong class="ph b">Chart</strong>.</li>
<li class="li">In the <strong class="ph b">Chart</strong> menu, check <strong class="ph b">Stacked</strong>. This ensures that results for each group you select are shown in the same chart column.</li>
</ol>
<p class="p">The final result will look similar to the example below:</p>
<p class="p"><img alt="" class="image img-screenshot" id="topic_wg4_wpc_cjb__image_bhh_htc_cjb" src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/duration_recipe_6.png" width="850"></p>
<p class="p"> </p>
</div>
</div>
</div>
<div aria-labelledby="ariaid-title11" class="topic nested1" id="topic_bny_rzb_l3b">
<h2 id="h_01HGXE62C8EAS2TREQ4CAF0BZP"><span style="font-weight: 400;">Calculating how long tickets sit with their last support group</span></h2>
<p>To calculate how long a ticket sits with its last group, <a href="https://support.zendesk.com/hc/en-us/articles/4408824243738#topic_hjf_hdb_hv">create a standard calculated attribute</a> to identify the time of the last assignment to a group. Then, <a href="https://support.zendesk.com/hc/en-us/articles/4408824243738#topic_g2g_hdb_hv">create a standard calculated metric</a> using the <a href="https://support.zendesk.com/hc/en-us/articles/4408834558746-Explore-functions-reference#topic_rpl_mlf_dhb:~:text=DATE_DIFF(_date%2C_date%2C_date_part)">DATE_DIFF() function</a> to calculate it.</p>
<h3 id="h_01HE2DVQY1ACVJXMTDWBD0HP98">Creating the attribute to capture the last group assignment</h3>
<ol>
<li>In your report, click the Calculations menu (<img alt="Calculations" src="https://support.zendesk.com/hc/article_attachments/7856565263258">) and select <strong>Standard calculated attribute</strong>.</li>
<li>Give the attribute a descriptive name, such as <strong>Last group assignment</strong>.</li>
<li>In the <strong>Formula</strong> field, enter the following:
<pre><span style="color: #1d1c1d; font-family: Monaco, Menlo, Consolas, ' Courier New' , monospace; font-size: 12px; font-style: normal; font-variant-ligatures: none; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: pre-wrap; background-color: rgba(29, 28, 29, 0.04); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">IF ([Changes - Field name]="group_id") <br>THEN DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name]) ENDIF</span></pre>
</li>
</ol>
<p class="p1"><img src="https://support.zendesk.com/hc/article_attachments/7856559866010"></p>
<h3 class="p1" id="h_01HE2E4CNC8D3YRS974NB655X8">Creating the metric to calculate how long the ticket was assigned to the group</h3>
<ol>
<li>In your report, click the Calculations menu (<img alt="Calculations" src="https://support.zendesk.com/hc/article_attachments/7856565263258">) and select<strong> Standard calculated metric</strong>. </li>
<li>Give the metric a descriptive name, such as <strong>Last group assignment to resolution</strong>.</li>
<li>In the <strong>Formula</strong> field, enter the following:
<pre><span style="color: #1d1c1d; font-family: Monaco, Menlo, Consolas, ' Courier New' , monospace; font-size: 12px; font-style: normal; font-variant-ligatures: none; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: pre-wrap; background-color: rgba(29, 28, 29, 0.04); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">DATE_DIFF([Ticket solved - Timestamp], [Last group assignment], "nb_of_minutes" )</span></pre>
</li>
</ol>
<p><img src="https://support.zendesk.com/hc/article_attachments/7856559866138"></p>
<h3 id="h_01HGXF9GJTDZM2YQWJ5W0H7F60">Adding the attribute and metric to your report</h3>
<ol>
<li>In the <strong>Metrics</strong> panel, click <strong>Add</strong> and select the metric you created above (<strong>Last group assignment to resolution</strong>, in the example).</li>
<li>
<a href="https://support.zendesk.com/hc/en-us/articles/4408846897178#topic_ycz_vxv_ngb">Change the metric aggregator</a> to <strong>AVG</strong>.<strong><br></strong>
</li>
<li>In the <strong>Rows</strong> panel, click <strong>Add</strong> and select <strong>Ticket ID</strong> and <strong>Ticket group</strong>.<strong><br></strong>
</li>
<li>In the <strong>Filters</strong> panel, click <strong>Add</strong> and select the attribute you created above (<strong>Last group assignment</strong>, in the example).</li>
<li>(Optional) For a cleaner-looking report, click the <strong>Capturing the last update</strong> attribute and then exclude <strong>NULL</strong> values and include only <strong>Solved</strong> and <strong>Closed</strong> statuses. For help, see <a href="https://support.zendesk.com/hc/en-us/articles/4408825475354#topic_enj_1pk_zwb">Filtering by an attribute in the Filters panel</a>. <br><img src="https://support.zendesk.com/hc/article_attachments/7856559866266">
</li>
</ol>
<h2 id="h_01FAK1QE1JX3F4M0HGQB8MXEY6"><span style="font-weight: 400;">Calculating duration of time for custom ticket fields</span></h2>
<p><span style="font-weight: 400;">Reporting on the duration of </span><a href="https://support.zendesk.com/hc/en-us/articles/4408883152794"><span style="font-weight: 400;">custom ticket field values</span></a><span style="font-weight: 400;"> is very similar in concept to </span><a href="#topic_y1x_rzb_l3b"><span style="font-weight: 400;">Calculating how long tickets sit with a support group</span></a><span style="font-weight: 400;">. The important difference here is that you’ll need to find a specific value to use in your custom metric formula in order to return the appropriate results.</span></p>
<p><span style="font-weight: 400;">For example, let’s say you’ve created a custom ticket field called Type of Support, with possible values of Beginner Support, Intermediate Support, and Advanced Support. You want to report on how long tickets spend with the Advanced Support value applied.</span></p>
<p><span style="font-weight: 400;">To get started, find the ID of the Type of Support field, and then use that ID to find the value of the Advanced Support value, which you can use in your custom metric formula.</span></p>
<h3 id="h_01HGXE36CQY3PZ4X67ZG3YGSHN"><span style="font-weight: 400;">Finding the specific ticket field value</span></h3>
<p><span style="font-weight: 400;">These steps are similar to those in </span><a href="#topic_t5z_clj_l3b"><span style="font-weight: 400;">Discovering your group ID numbers</span></a><span style="font-weight: 400;">. Except here, you’re not going to find the ID of a group; you’re going to find the ID of a custom ticket field, and then find the specific value of the ticket field value that you want to report on. </span></p>
<p><strong>To find the ticket field ID</strong></p>
<ol>
<li aria-level="1" style="font-weight: 400;"><span>In <a href="https://support.zendesk.com/hc/en-us/articles/4408838272410">Admin Center</a>, click the <strong>Objects and rules</strong> icon (<img src="https://zen-marketing-documentation.s3.amazonaws.com/docs/en/ac_objects_rules_icon.png">) in the sidebar, then select <strong>Tickets &gt; Fields</strong>.</span></li>
<li aria-level="1" style="font-weight: 400;">
<span style="font-weight: 400;">In the </span><strong>Field ID</strong><span style="font-weight: 400;"> column, find the ID number of your custom ticket field (in this example, Type of Support). Make a note of this number, which you’ll need in the following step.</span>
</li>
</ol>
<p><strong>To find the value of the ticket field value </strong></p>
<ul>
<li aria-level="1" style="font-weight: 400;">
<span style="font-weight: 400;">In your web browser, visit the following URL: </span><strong><em>subdomain</em></strong><strong>.zendesk.com/api/v2/ticket_fields/</strong><strong><em>&lt;ticket field ID&gt;</em></strong><strong><br></strong><span style="font-weight: 400;"><br></span><span style="font-weight: 400;">(replace </span><em><span style="font-weight: 400;">subdomain</span></em><span style="font-weight: 400;"> with the name of your Zendesk subdomain, and replace </span><em><span style="font-weight: 400;">&lt;ticket field ID&gt;</span></em><span style="font-weight: 400;"> with the ID you noted previously)</span>
</li>
</ul>
<p><span style="font-weight: 400;">In the example, you would see something like the following:</span></p>
<ul>
<li aria-level="1" style="font-weight: 400;"><span style="font-weight: 400;">“id”: 360048826392,</span></li>
<li aria-level="1" style="font-weight: 400;"><span style="font-weight: 400;">“name”: “Advanced Support”,</span></li>
<li aria-level="1" style="font-weight: 400;"><span style="font-weight: 400;">“raw_name”: “Advanced”,</span></li>
<li aria-level="1" style="font-weight: 400;">
<span style="font-weight: 400;">“value”: “</span><strong>advanced_support</strong><span style="font-weight: 400;">”,</span>
</li>
<li aria-level="1" style="font-weight: 400;"><span style="font-weight: 400;">“default”: false</span></li>
</ul>
<p><span style="font-weight: 400;">The value to use in the custom metric formula is “advanced_support”.</span></p>
<p><span style="font-weight: 400;">If you were to use “Advanced Support” instead, the formula would technically be valid, but it wouldn’t return any results.</span></p>
<h3 id="h_01HGXE36CQTKARVZQFHJQDNJS5"><span style="font-weight: 400;">Creating the custom metric and using it in a report</span></h3>
<p><span style="font-weight: 400;">Following the example, you want to see how long a ticket spends with the Advanced Support value set. The formula for that would look like the following:</span></p>
<pre><span style="font-weight: 400;">IF ([Changes - Field name]="Type of Support")</span><br><span style="font-weight: 400;">AND ([Changes - Previous value] = "advanced_support")</span><br><span style="font-weight: 400;">THEN VALUE(Field changes time (min))</span><br><span style="font-weight: 400;">ENDIF</span></pre>
<p><span style="font-weight: 400;">For more detailed instructions, see </span><a href="#ariaid-title6" target="_self"><span style="font-weight: 400;">Creating the custom metric</span></a><span style="font-weight: 400;"> earlier in this article. </span></p>
<p class="title topictitle2"><span style="font-weight: 400;">The last step is to create a report using your custom metric. For instructions, see </span><a href="#ariaid-title7" target="_self"><span style="font-weight: 400;">Using the custom metric in a report</span></a><span style="font-weight: 400;">.</span></p>
<h2 class="title topictitle2" id="ariaid-title11">Next steps</h2>
<div class="body">
<p class="p">For more information about the metrics and attributes you can use with the Updates history dataset, see <a class="xref" href="https://support.zendesk.com/hc/en-us/articles/4408827693594-Metrics-and-attributes-for-Zendesk-Support" rel="noopener" target="_blank">Metrics and attributes for Zendesk Support</a>.</p>
</div>
</div>
<p>
</p><script id="docs-schema-markup" type="application/ld+json">{
"@context": "https://schema.org",
"@type": "TechArticle",
"@id": "https://support.zendesk.com/hc/en-us/articles/4408831024922",
"url": "https://support.zendesk.com/hc/en-us/articles/4408831024922-Explore-recipe-Reporting-on-the-duration-of-fields",
"headline": "Explore recipe: Reporting on the duration of fields",
"description": "You can create reports to measure the duration tickets spend in various statuses, support groups, or custom fields by using calculated metrics and attributes with the Updates history dataset.",
"articleSection": "Ticket reporting recipes",
"datePublished": "2021-10-16T03:44:07+00:00",
"dateModified": "2025-09-06T00:04:32+00:00",
"inLanguage": "en-US",
"mainEntityOfPage": {
"@type": "WebPage",
"@id": "https://support.zendesk.com/hc/en-us/articles/4408831024922"
},
"author": {
"@type": "Organization",
"name": "Zendesk",
"@id": "https://www.zendesk.com"
},
"publisher": {
"@type": "Organization",
"name": "Zendesk",
"@id": "https://www.zendesk.com"
},
"isPartOf": {
"@type": "WebSite",
"name": "Zendesk Help",
"@id": "https://support.zendesk.com/hc/"
},
"about": {
"@type": "SoftwareApplication",
"name": "Zendesk",
"@id": "https://www.zendesk.com",
"softwareAddOn": {
"@type": "SoftwareApplication",
"name": "Explore",
"@id": "https://support.zendesk.com/hc/en-us/sections/4405298856346"
}
}
}</script>