Insights recipe: Troubleshooting custom metrics for the Ticket Events data model

Insights has now been removed for most customers. If you used Insights before, you'll now need to move to Explore, the latest generation of reporting for MorgWard products.

In Insights, the Tickets model only looks at the ticket's current state when being calculated. The Ticket Events looks at all changes made to tickets (see Building custom metrics for the Events model in Insights). This produces a large amount of data and can cause issues when creating custom metrics and loading reports.

This article will troubleshoot the following issues for creating reports using Ticket Events:

• Dates outside of filtered date range are showing a zero value
• Report will not render

Dates outside of filtered date range are showing a zero value

This problem can occur when applying filters to set the date range on a report. The report below uses a Date (Ticket Created) filter to restrict the date range to October 2017 through November 2017, but zero values are appearing for dates outside of the set range.

This is caused by using an attribute designed for the Ticket Events model, but a filter designed for the Tickets model. The same problem occurs if the attribute is designed for the Tickets model and filter is designed for the Ticket Events model. The filter for the report above uses Date (Ticket Created), but the report attribute is Date (Event). Since the two attributes are from different data models, it results in zero values appearing.

To remove zero values on outside date ranges

• Ensure that the attribute in the How panel and attribute used in the filter are from the same model. 

Report will not render

Since the Ticket Events dataset is so large, it might time out before it can be successfully cached. In this case, the Ticket Events dataset includes 0 values by adding SELECT IFNULL to the front of the metric and will take a long time to calculate. Additionally, each time the report is run on a new machine or for the first time for an agent, the report might time out or present the following error message. 

There are two possible solutions to this issue.

To render reports with Ticket Events

• Create the metric using a Tickets model metric rather than a Ticket Events model metric.
• Or, build filters into the metric itself to limit the amount of data the metric is looking at, such as the formula below:

  SELECT IF((Date (Event) > THIS) OR(Date (Event) < 02/29/2016)) THEN -1 ELSE 1 END

You can then use this metric in a custom report as a filter for your data and only include data going back to the date your account was created, and no further.