AVG Tickets Created per Day Month
RespondidaHi,
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
-
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:
Hope this helps Marcel and let us know if you run into any issues or have additional questions (:
Warm Regards,
Eric
-
Spot on article. Thank you very much.
Publicação fechada para comentários.
2 Comentários