Number of tickets last month against the average monthly volume

Forum|Forum|5 years ago
January 14, 2021
2 replies
0 views

Galina

Hello,

I would like to get the number of tickets created last month (that is easy to do) but also compare it against the monthly average number of created tickets (based on the last 12 months) - show it as a trendline.

How to get the monthly average number of tickets created (over the last 12 months)?

1) I tried to create a Date range calculated metric first (for 12 months in the past)

2) Then created a Standard calculated metric: divided the metric from above by 12 (months).

I get an error "Only partial results are visible for this query because the underlying SQL query returns more than 50,000 rows. To limit performance issues, each delegated SQL query is restricted to 50,000 rows" and the trendline shows some crazy numbers (in millions).

Would appreciate any help. Thank you!

Taylor18
Forum|Forum|5 years ago
January 14, 2021

Hi Galina,

Thanks for reaching out! I gave this a try in my test account and came up with this:

First, you'll want to create a standard calculated metric to find the monthly average for all time. You can use this formula to do so:

COUNT(Tickets)/DCOUNT_VALUES([Ticket created - Month])

Be sure to check compute separately. Next, we'll want to create a date range calculated metric computed from this custom metric to only return monthly averages for the last 12 months.

Next, we can add the default COUNT(Tickets Created - Last Month) metric and your custom date range calculated metric to the metrics section. Be sure to keep Tickets Created - Last Month listed as the first metric.

Lastly, we'll want to change this to a KPI visualization so we can show the variation between tickets created last month and your monthly average.

After changing this to a KPI chart, you can go to chart configuration>>chart and select show variation. You can choose to display this variance as a percentage or difference.

If this isn't quite what you're looking for let us know and we'd be happy to take another look!

Best,