Accurate JQL to measure opened/closed tickets?

[Oliver Clark]

Hi, 

I'm trying to improve the quality of our internal reporting. One of our key metrics is opened support tickets vs closed, which I report to people on a weekly and monthly basis. 

I've had a great deal of trouble finding accurate and internally coherent methods for these figures however, which feels counter-intuitive given the simplicity of the query. I'll try to explain the issues I've encountered so far:

• I initially used JSD reporting tools to query open/closed tickets. For the former I would use the 'created' series and simply define the project in the query. For the latter i would do the same with the 'resolved' series. The data I get out of this however, does not match that which i get from manually running functionally identical queries in the issue navigator. My theory on this is that it is due to the reporting module being limited to a single calendar year though the specifics of this I am not certain on. 
• The reporting module is also limited to point in time reviews. I'm expected to provide statistics that line up week to week. Eg 
  
  Week 1 - 300 open
  Week 2 - 20 opened, 30 closed
  Week 2 total - 290 open
  
  The point in time nature of the report i believe causes overlap or gaps between reports on a week to week basis. For example, if I produce week 1's report at 1320 on Monday but week 2's report at 1030 on the following Monday, it seems that week 2's report will contain tickets raised before the last report went live. As such, when comparing week to week ticket volume changes to opened/closed levels, it's rare that the numbers line up. 
• In lieu of this uncertainty I've attempted to use manual queries in Issue Navigator for my reporting and exporting these to excel to trim them to the exact time frame needed (ie. 1320 week 1 - 1030 week 2). However even when employing these steps I still encounter discrepancies between the volume of opened/closed tickets and the number of open tickets. My queries for this are
  
  Project = servicedesk and created date = [daterange]
  
  and:
  
  Project - service desk and resolved date = [daterange] 
  
  I'm wondering if this could be due to tickets being reopened and thus adding to the total open tickets count without being counted towards the created count. 

Of the 3 metrics I'm working with here, the only one I'm really confident in is that of currently open tickets.  If anyone could advise on a more reliable way of extracting this information from Jira it would be really appreciated. 

Thanks,

Ollie

[Grace Khan]

I also have this issue and would like to find others who have found the actual # of tickets are open for a given month

[Tessa Tuteleers]

Hi Oliver, 

You could just use the created vs resolved chart on a dashboard and set it to weekly. You can set it on count and it will show you echt created an resolved issue for each week + below that a bar showing the currently open issues if you check the "Show trend of unresolved" issues

However, you can't export the numbers from that in a nice little table like you show above but you could just click the bullets and go to the list of issues. 

I did just check as well, re-opening issues and resolving them again only makes them appear once in the chart, i.e the last time. So it could be perfectly possible that the statistics you keep "offline" from the previous weeks are not accurate anymore because of re-opening of issues. 

2 possible workarounds: 

- Don't store the data offline, only look at live date (which - I know - can be difficult)

- Set a "no re-opening" policy with a timeframe. Where you let tickets that are resolved close automatically when they are resolved for e.g. 2 weeks (you will need an automation add-on for that). You won't have a transition out of the closed status so your ticket is closed forever, new questions should create a new ticket (which actually is best practice as well..) Then you know for sure that after 2 weeks, you have the correct and final numbers for that timeframe. 

Hope this helps ;)

Cheers, 
Tessa

[Oliver Clark]

Hi there, I experimented using the Dashboard open/resolved chart, but unfortunately I'm still unsure if this fits the use case here, as it seems to exclusively provide data counting backwards from the point in time it's being viewed. So weekly data for example, were i to run that now, would show weeks going Friday to Friday. As above, time of day is also a factor and so I don't know if this works for me :( 

it's this that's prompted me to use issue search exports more and more. I should clarify that I'm not comparing historical raw data so much as the reports I compile from it. The leadership team here monitor our trends week on week; and each week I struggle to resolve the number of open tickets with the opened/resolved counts; even when I control date ranges down to the minute by trimming my exports and recording the cut off time each week. 

The no-reopening policy would be something interesting to implement but I can see that being quite contentious with some of our clients who get very protective (for want of a better word) of the control they have over their tickets. Definitely a change I'd have to escalate first. I'm wondering if it would be possible to set automation up to clear the resolved date field upon an issue transitioning back into an open status, but I'm still unsure if that would sort my issue. 

It might just be that there's so many little variables that this is impossible to nail down exactly so that everything adds up week to week. But if that's the case I need to find out how I square this with my managers haha. I'll keep digging but if you have any more thoughts on this do let me know. Thanks for your help so far

Cheers, 

Ollie  

[Tessa Tuteleers]

Hi Ollie, 

interesting problem indeed. 

However from the chart, I don't think that is correct. If I view the carts, if I set it to weekly it cuts of sunday  @ midnight (the american first day of the week). To change that to monday you could check this extensive thread.

The resolution date is cleared as soon as the resolution is cleared again from the issue, so ensure that when an issue is re-opened, the resolution is cleared as a post-function as well. 

Otherwise, I don't really have any more ideas. Please let me know if you figured it out however, you peaked my interest with this!  :D

Cheers!