Jump to content

Recommended Posts

Posted

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:

 

image.thumb.png.33a9e0e2178d4961186806b1bfbfc035.png

What am I doing wrong?

 

Thanks

Posted

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

Posted

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

 

Posted

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.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...