Joyce Posted November 27, 2017 Posted November 27, 2017 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
Dan Munns Posted November 27, 2017 Posted November 27, 2017 @Joyce Does your value column not have to be r_reopencount ? What is your Query where clause?
Joyce Posted November 27, 2017 Author Posted November 27, 2017 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 Posted November 28, 2017 Posted November 28, 2017 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 Posted November 29, 2017 Author Posted November 29, 2017 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.
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now