What JQL query to get tickets NOT IN Done over time?

[Shaun Shue]

I have a dashboard gadget that gets the tickets NOT IN (Done), which works great for getting the number of outstanding tickets at the current moment. However, I'd like to display the weekly history of this, and I'm not figuring it out. What JQL would I use in a Custom 1D Line Chart, to show what this number was over the past few weeks through to today?

Thanks in advance.

[Jason Rittenhouse · Accepted solution]

I'm not aware of a way to do this natively in Jira because the historical state of issues is not retained outside of a few fields with history (Assignee, Fix Version, Priority, Reporter, Resolution, and Status) and the reporting features are very limited. And while resolution might be a historical field, "not-resolved" is not and that's what you need.

However, with the right reporting tool and a little effort, you could create a custom report that is charted over time. You'd need a third-party reporting tool that specifically gives you the ability to chart by JQL (eg Custom Charts; I assume you could build this in EazyBI as well) and then you'd need a pair of JQL queries for each time frame you want to report on.  For example, if you wanted this reported monthly, over 6 months of history, you'd need 12 queries. But, once you set this up, it would be set it and forget it. Here's how you could do it:

The queries for last month would look like this:

1. project = XXX and resolved < startOfMonth()
   1. this gives you every ticket resolved before the start of the current month. Save this filter, eg "Resolved last month"
2. project = XXX and created < startOfMonth() and filter != "Resolved last month"
   1. this query is what you put in your chart, with the label "last month". What this does is ignore anything created this month since it's just a number for last month. Then it will give you every ticket that doesn't fall in the first filter above which should leave you with every unresolved issue as of the end of last month. 
   2. The reason you have to do it this way is because there very well may be tickets that were open last month but that are resolved now (moreso when you're looking 6 months back). So you have to query around that by taking the inverse of the first query above, further excluding anything created new since then as well.

You would then repeat this for each previous month, incrementing the "startOfMonth" by one each time in both queries. For example:

The query for 2 months ago would look like this:

1. project = XXX and resolved < startOfMonth("-1")
   1. this gives you every ticket resolved before the start of last month. Save this as another filter, eg "Resolved 2 months ago"
2. project = XXX and created < startOfMonth("-1") and filter != "Resolved 2 months ago"
   1. Like before, this query goes in the "chart by" of your chart with the label "2 Months Ago".

Here's a screenshot showing how this would be set up in Custom Charts (note, the "today" query does not need a second query, it's just a simple query to get current unresolved issue):

(For anyone else reading this, I'm not affiliated with Custom Charts in any way. It just happens to be the tool my company uses)

[Shaun Shue]

Thanks!!

[Marita Norvele]

Hello @Shaun Shue,

As @Jason Rittenhouse  mentioned (thanks, Jason!), this report is possible in eazyBI. You can check this sample report showing unresolved issues by status over time:
https://docs.eazybi.com/eazybi/learn-more/learn-eazybi-through-sample-reports/samples-jira-issues/unresolved-issues-by-statuses-over-time

Let me know if you have any additional questions.

Best,
Marita support@eazybi.com