How to determine number of unique users who closed cases in a month

[Peter Lohret]

Hello!  My department wants to use as a metric "total number of cases closed / # of users who closed cases".  So if we have 50 people in our department, but only 40 of them closed cases last month, and we closed 80 cases, it would be 80/40 = 2.

It would be fine if I could have 2 gadgets and do the math, and I can get the # of cases closed easy enough, but I can't find a gadget that will give me the # of users who closed a case by month.  I've been doing this by dumping data to excel and then just counting rows but I'm hoping someone has a better answer.  Thanks!

[Tuncay Senturk _Snapbytes_]

Hi Peter,

I don't think you can get the exact number in a dashboard gadget without implementing a plugin or writing some code.

From my understanding, you need to group first by month then user and see the number of issues closed by these users within months.

@Jack Brickey 's solution is nice but if the assignee of the issues changes in time that won't reflect the accurate numbers.

I worked with a few clients who had similar requests and I offered them Enhancer Plugin's two-dimensional filter which can be seen as below. First, you have to define Closed User and Date custom fields which would index/display the user and the date that the issue was closed. So it won't display results according to the assignee field. It will do it by the users who transitioned the issue to the selected Closed status.

Sorry, I could not offer an out-of-the-box solution as they can end up with inaccurate results. Please bear in mind that I'm one of the folks behind Enhancer Plugin, and if you need further assistance on this one, please let me know.

Tuncay

[Jack Brickey]

one idea....

create a filter e.g. project = myproject and resolutiondate >= startofmonth("-1") and resolutiondate < startofmonth. This will allow you to look at the previous months metrics. use this on the 2-dimensional gadget and set the y-axis to assignee and maybe the x-axis to status. 

downsides:

• not a month by month view
• assumes assignee = the one that closed the issue (likely the case)

[Peter Lohret]

That's basically what I'm doing when I dump it to excel and count the rows, so that would save me that step, except I'd have to manually count each assignee instead of just selecting the column.  But I was hoping something that can just give me a number, or better yet a month-to-month number.

[Jack Brickey]

Not sure I understand. The solution I offered doesn’t require excel. The gadget would give you the number of issues closed by assignee for the previous month. If desired you could create a filter and associated gadget for each month. There isn’t anything manual here beyond creating the filters and building the gadget. 

[Peter Lohret]

I'm not looking for cases closed by assignee, I'm looking for the number of unique users who closed cases in a month.  So I can do what you're saying, get a filter results gadget, then count the rows to see how many users closed a case that month, but ideally I'd like something that can tell me in a graph or table "X number of users closed a case in January, Y in February, Z in March, etc"

[Jack Brickey]

Have you looked at the 2-d gadget I recommended?

[Peter Lohret]

Ah ok sorry didn't see the total down at the bottom.  Yeah that gets me for one month at a time and I can do a filters for month -1, -2, etc to get the last 6 months, then do the math.  If that's the best we can do that's the best we can do.  Thanks!

[Jack Brickey]

again you could create a gadget per month but pretty messy IMO. If I were doing this regularly I would consider the following:

• use the official excel extension and have it drop in the latest data and use a pivot table or charting
• hook into MS PowerBI
• look at an addon e.g. what Tuncay mentioned or EasyBI, or the other addons that support better charting.