Average tickets reports

[Joyce]

Hi 

I am trying to create a measure for the following:

Average number of tickets reopened per month

Average Number of tickets resolved per month

I have set up An average measure, as below, but I am getting 0 values:

 

What am I doing wrong?

 

Thanks

[Joyce]

@Bob Dickinson

[Dan Munns]

@Joyce

Does your value column not have to be r_reopencount ?

What is your Query where clause? 

[Joyce]

My query clause is just selecting  request type (h_requesttype='Service Request'). I have also try adding r_Reopencount !=0, but |I am still getting 0 average.

 

The r_reopencount, is counting number of times a ticket has been reopen ( so one ticket can be reopen more than once). The average I am after is of number of Service request re-openned monthly

[Guest]

Hi @Joyce

The issue here is I think that you are using "Average" in a measure for a value that only has two outcomes (being reopened or not)

A request being reopened is either true or false (or 1 or 0). In 1 month, if you had 50 requests, and only 2 of them were reopened, the average of this (with the 1 and 0 values) would be 0.04. This doesn't really give you much useful information. 

A better report in this example would be The Number of Service Requests Reopened Monthly. For this, just change the Value Aggregate from Average to Count. 

If you wanted the average, this would be better suited to a widget, getting the average across a number of months e.g. "Average number of requests reopened.
The SQL needed for this is below (but I'm not sure this can replicated in a widget yet):

SELECT AVG(a.count) AS avg 
FROM ( SELECT count(*) AS count, MONTH(h_reopendate) as mnth
FROM h_itsm_requests where h_reopendate is not null 
GROUP BY mnth) AS a

 

Kind Regards

Bob

 

[Joyce]

Thanks Bob.

I did the above, just getting the count, then average it outside on a piece of paper.

 

I will try your sql query above and see if it works.

 

Thanks for your assistance.