AVG Tickets Created per Day Month

Answered

Posted Jun 21, 2019

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

2 comments

Date

Eric Gao

Zendesk Customer Care

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