We need to find resolved and closed tickets within 21 days in JQL

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi,

Need Help in JQL

Can we find the Resolved and Closed Tickets  within in 21 days created.

Thanks

Sagar

[Valeriia_Havrylenko_SaaSJet]

Hello @Venkata Sagar Ganesh Rao Mahendrakar 

I will be glad if my article about JQL helps you. 

Or try status ="Awaiting user" and NOT status changed to "awaiting user" after -21d

If not, as an alternative , I guess you can try Time in Status for Jira Cloud  (developed by my team) if you are already switching to cloud version. Our Add-on get your Cycle and Lead time by setting Status Groups.  You can configure it from your account at the Column Manager with the drop-down list of the statuses. A custom report can be placed on the main Jira dashboard with the special Gadget tool, and you can export data in XLSX or CSV formats.

Also Time Between Statuses (developed by my team) will be an option for you too. This add-on, which measures connections in the workflow, through a transition time in specific issues. You can count Cycle and Lead Time by setting start/stop and pause statuses in the configuration manager. To detail the calculation conditions you should select the first/last transition to/from status.

Add-ons have a 30-day free trial version and free up to 10 users. 
Please, let me know if you have any questions

Hope it helps 😌
Valeriia

[Mehmet A _Bloompeak_]

Hi @Venkata Sagar Ganesh Rao Mahendrakar

As an alternative, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For further details, you can have a look at Status Time Reports How to Videos.

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

If you are looking for a completely free solution, you can try the limited version Status Time Free.

Hope it helps.

[Emre Toptancı _OBSS_]

Hello @Venkata Sagar Ganesh Rao Mahendrakar,

The data needed for such a measurement is present in each issue's history but Jira does not give this as a ready-to-use report. For that, you will need to use a marketplace app.

Our team at OBSS built Time in Status exactly for this. It is available for Jira Server, Cloud, and Data Center.  

Time in Status mainly allows you to see how much time each issue spent on each status or each assignee. 

   

You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

With Time in Status, you can also filter issues based on these times. For example you can get the issue where the resolution time is greater than 21 days.

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer or average resolution time per sprint, week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) or sprints is particularly useful here since it allows you to compare different time periods or see the trend. 

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects for Jira Cloud.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

  

https://marketplace.atlassian.com/apps/1211756/

EmreT

[Trudy Claspill]

Hello @Venkata Sagar Ganesh Rao Mahendrakar 

Jira does not natively allow you to compare two date fields directly in a JQL statement. You might be able to do that with a third party app that extends JQL functionality. Are you open to acquiring such an app?

How often do you need to get this report?

Will the time period always be 21 days?

What do you want to do with the information when you have it? Do you need to distribute the list of issues to people? Do you need to show the data on a dashboard in Jira?

Are you using Jira Server or Jira Data Center? Do you have the Automation for Jira app available in your system? If so, you might be able to construct a work around where you use Automation for Jira to accomplish this.

[Venkata Sagar Ganesh Rao Mahendrakar]

Hello @Trudy Claspill .

Thanks for the Reply!!

We are using Jira Data Center. 

Regards
Sagar

[Venkata Sagar Ganesh Rao Mahendrakar]

But we are not using Automation for Jira app. 

Thanks for the Suggestion.

[Trudy Claspill]

What version of Jira Data Center are you using?

With version 9.x, Automation for Jira is automatically included.

For earlier versions it is a free app you can add.

https://marketplace.atlassian.com/apps/1215460/automation-for-jira?hosting=datacenter&tab=overview

I'm not positive that a solution could be created using the Automation feature for Jira DC. I can readily think of a solution that would work in Jira Cloud, but Automation for Jira DC does not have the same functions as Automation for Jira Cloud.

Would the time period always be 21 days?

Are you open to acquiring a non-free app to address this need?

[Trudy Claspill]

Hello @Venkata Sagar Ganesh Rao Mahendrakar 

Were you able to find a solution to your need?

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi Trudy 

We are using Jira Server 8.20.6.

Yes, it is always 21 days 

Thanks for your reply!!

We stopped troubleshooting this.

Thanks

Sagar

[Trudy Claspill]

With Automation for Jira (which is a free app from Atlassian) you could set up a rule that would be triggered when the rule is transitioned to specified statuses and could calculate the difference between that transition date and the creation date. The difference could be recorded in a custom field that you could use later for filtering.

For issues that had previously transitioned you could use another rule to look up those issues and retroactively fill in the custom field.

You would probably also want a rule that would be triggered if the issues reverted to an earlier status to clear the field.

[Iryna Komarnitska_SaaSJet_]

Hi @Venkata Sagar Ganesh Rao Mahendrakar 

Try to use this query status changed to (Done, Closed) after -21d

[Venkata Sagar Ganesh Rao Mahendrakar]

Hi @Iryna Komarnitska_SaaSJet_ ,

Thanks for the Reply!!

We wanted to know the tickets which are resolved within the 21 days after the creation. 

Could you please help on that.

Thanks 

Sagar