Cumulative amount of time an issue has spent in a state

[Zsigmond Molnar]

Hi,

 I would like to query the cumulative amount of time an issue has spent in a state.

The issue can be in the same state more than once. (e.g. ‘in progress’ or ‘awaiting feedback’)

Therefore, I would like to know the total amount of time spent the issue in a state, not just the most recent occurrence.

How can I retrieve this information for a single issue?

Also, how can I display the minimum, maximum, and average cumulative time for all issues on a Jira dashboard.

Thank you in advance for your support.

Sincerely,

Zsigmond Molnar

[Hannes Obweger - JXL for Jira]

Hi @Zsigmond Molnar

without additional tooling, I'm afraid this going to be a bit tricky. I have seen attempts to do the calculation in Jira Automation (see this article as a starting point), and of course you can always look at the issue history (either in the UI or via the REST API) and do the math manually.

There are, however, a number of Marketplace apps that operate in this space. If the Marketplace is an option for you, may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called history columns that aren’t natively available, including time in [status], time between [status] and [status], and many more. For these columns, you can also configure if you want to see e.g. the first, the latest, or the cumulative time in a status.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. With sum-ups, you can easily calculate the min, max, or average time of your issues.

Any questions just let me know,

Best,

Hannes

[Gizem Gökçe _OBSS_]

Hello @Zsigmond Molnar ,

There is no built in solution in Jira for this. So in order to track the time spent on each status I suggest you use a marketplace app. Timepiece - Time in Status for Jira which is developed by my team at OBSS, has a report type that will meet your need.  Our app is available for both Jira Cloud, and Data Center. 

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

Status Duration report (please see the screenshot above) which shows how much time each issue spent on each status. 

In your report, if an issue visits a status more than once, you can choose which visits to use in your calculations. The default is the total duration of all visits. 

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.

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. And the app has a REST API so you can get the reports from Jira UI or via REST. Also you can export the reports in to various formats easily. 

Visit Timepiece - Time in Status for Jira to explore how our JIRA add-on can revolutionize your metrics measurement process. Enjoy a 30-day free trial to experience the full range of features.

If you wish, you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Hope it helps,

Gizem

[Amay Purohit_RVS]

Hi @Zsigmond Molnar 

Not sure if this can be achieved with native Jira reports. If you would be interested in a mktplace add-on for more detailed and enhanced reporting, please try out

Time in Status Reports 

The add-on provides the time in status of the entire issue workflow and there are 20+ reports in the app which help to meet a wide range of use cases. Also these reports can be added as dashboard gadgets.

Other report details here

Do try it out.

Disclaimer : I work for RVS, the vendor for this app

[Danut M _StonikByte_]

Hi @Zsigmond Molnar,

You cannot achieve this at the JQL level. 

But, fortunately,  there is a good solution for getting this issue info. Our Great Gadgets app offers a WIP Aging Chart gadget that shows the issues from a board/filter/JQL by their age, where age is the total time of an issue in its current state along with its previous occurrences. If you hover one of the issues, you will see the age in tool-tip and the number of occurrences. 

The gadget can display the average for the overall issues and allows you to set a threshold to easily identify the issues that took longer (the ones in red-color).

In addition, it offers a data tab with a detailed report, which can be  exported in CSV.

See more about this gadget in this article: https://community.atlassian.com/t5/Jira-Software-articles/Building-a-powerful-Kanban-dashboard-in-Jira-with-Great-Gadgets/ba-p/1664331 

Hope this helps. If you have any questions, please contact support@stonikbyte.com.

Danut

[Mehmet A _Bloompeak_]

Hi @Zsigmond Molnar

There is no built-in solution for time in status info in Jira. If you prefer using a marketplace app, 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 your case, you can have a look at Time in Status for Each Issue report.

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, resolution 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 Reports Free.

If you have any questions, feel free to contact us via support@bloompeak.io or schedule a demo with us.

Hope it helps.

[Iryna Komarnitska_SaaSJet_]

Hi @Zsigmond Molnar, 

JQL does not calculate the value of how long an issue has been in statuses.

It would help if you used third-party apps for this. If you don't mind this solution, I recommend using the Time in Status app by SaaSJet (my team developed this add-on).

So, you can get the report you need by using one of the main types of Time in Status reports.

With Column Manager, you can calculate the time spent in different statuses. 
For example, add or subtract statuses you don't want to consider (e.g., On Hold, Done).

We offer you 30-day free trial and invite you to book a demo call.

I hope you find it useful.