Question

AVG Tickets Created per Day Month

Forum|Forum|6 years ago
June 21, 2019
2 replies
0 views

Marcel15

Hi,

I am trying to build two custom metrics, which display AVG Tickets Created per Day – one for THIS Month and one for LAST Month (so I can compare both KPI, while the current month is still ongoing and not finished yet).

When trying to create the metric, I am getting the following error message:

Error at line -1 and column -1

Custom metric

COUNT(Tickets Created This Month) / 
IF (MONTH_NUMERIC(TODAY())=1) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=2) THEN 28 ELSE
IF (MONTH_NUMERIC(TODAY())=3) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=4) THEN 30 ELSE
IF (MONTH_NUMERIC(TODAY())=5) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=6) THEN 30 ELSE
IF (MONTH_NUMERIC(TODAY())=7) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=8) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=9) THEN 30 ELSE
IF (MONTH_NUMERIC(TODAY())=10) THEN 31 ELSE
IF (MONTH_NUMERIC(TODAY())=11) THEN 30 ELSE
IF (MONTH_NUMERIC(TODAY())=12) THEN 30 ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF ENDIF

FYI: COUNT(Tickets Created This Month) is a custom, Date range calcuated metric, which I created.

Funnily enough, COUNT(Tickets Created This Month) gets calculated correctly on its own, when creating a custom metric, same as the IF formulas give me the correct amount of days during a particular month when calculated alone, but when I put both together, I am getting the error message mentioned above.

Looking forward to getting some ideas here.

Best,
Marcel

This topic has been closed for replies.

Eric78
Employee
Forum|Forum|6 years ago
June 21, 2019

Hi Marcel,

Thanks for reaching out and hope your day is going well. If the goal is to compare the AVG # of Tickets Created per Day between this month and the previous month, we can leverage the default metrics/attributes in Explore to achieve this:

- SUM(Tickets created - Daily average) in the Metrics section; this allows us to look at the AVG # of Tickets Created per Day

- Ticket created - Month in the Rows section; this allows us to group the result by month

- We can further filter the Ticket created - month attribute by specifying the advanced date range to 1 month in the past to Today (as highlighted above); this will limit the result to only show data from from this month and the previous month.

We will now see the daily average by month in our query:

We can also specify the decimal place (Chart configuration > Display format > Custom > Decimal place) to get a more accurate result:

For validation, we can report on the daily # of ticket created for May and June (by Day of month) and calculate the daily average, as they should match up with what we have in the other query: