Is there a way to find issues that ere in a certain status for certain duration?

[Omri]

Hi,

I know "Time in Status" is a popular function and that in order to report that I will need a plugin.

But what if I want a specific result in JQL that will show me issues that were in a certain status for a certain duration? Is there that kind of functionality? 

Thanks

[Bill Sheboy · Accepted solution]

Hi @Omri -- Welcome to the Atlassian Community!

What problem are you trying to solve by doing that?  Or thinking about this another way, how would you work differently if you had that information right now?  Knowing this may help the community to offer other approaches to solve that problem.

Until that information is provided...

Doing what you ask would require checking the issue history to determine the duration in a status, and potentially adding up multiple values when an issue goes through the status multiple times.  This could happen when status changes, saving in a custom field, or dynamically.  This second approach is what the built-in Atlassian interpretation of a "control chart" does.

If you are looking for this information for a single project for a window of time, that "control chart" might help.  If you are looking at other workflow issues, looking at Atlassian's interpretation of a "cumulative flow diagram" might help.  If you need these measures stand-alone for queries, you may need to either build this yourself (with automation rules or the REST API) or investigate the marketplace for addon apps.

Kind regards,
Bill

[Omri]

Hi @Bill Sheboy 
Thank you for your answer.

It is a good question indeed. Right now I'm trying to understand if a specific department in our company (that uses several unique statuses) is meeting our KPI's and expectations from them. I agree queries is not the best way to do that.

Thanks!

[Emre Toptancı _OBSS_ · Accepted solution]

Hello @Omri ,

In short: No, JQL does not offer this functionality out-of-the-box. Yes, you will need a plugin for that.

And a longer answer:

JQL does not have this. Even if it did, it would be a very expensive JQL query to run since calculating this would require not only issues but also issue histories to be processed. Not recommended.

As Bill Sheboy pointed out, it is possible to build something with Automation rules or some custom development. but it will need to be repeated for each status duration. And as the number of statuses gets higher, it will be a nightmare to maintain.

That is why a marketplace app is recommended here. 

If you are OK with using a marketplace app for this, our team at OBSS built Timepiece - Time in Status for Jira for this exact need. 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. 

 

The app has Consolidated Columns feature. This feature allows you to combine the duration for multiple statuses into a single column and exclude unwanted ones. It is the most flexible way to get any measurement you might want. Measurements like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

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 (organization) 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.

  

Timepiece - Time in Status for Jira

EmreT

[Omri]

Hi @Emre Toptancı _OBSS_

Thank you for your answer.

I'll look at this app, it looks like a good option for me.

Thanks!

[Valeriia_Havrylenko_SaaSJet]

Hello @Omri 

Welcome to Atlassian Community! 

As an alternative, you can try Time between Statuses (server version) to calculate how long it takes an issue to move between statuses.

This add-on is developed by my SaaSJet team and it's free for teams up to 10 users.

Please, let me know if you have any questions.

Best regards,
Valeriia

[Mehmet A _Bloompeak_]

Hi @Omri

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.

• 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 free solution, you can try the limited version Status Time Free.

Hope it helps.

[Madhu_RVS]

Hi @Omri 

Welcome to the community !!

If you are fine with a mktplace app to get this data, try out our app.

Agile Tools : Epic Tree, Links Tree, Time in Status & Worklogs

With the status duration filter, you can filter for issues which were in a certain status for specific times.