Cumulative amount of time an issue has spent in a state

Question

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

Danut M _StonikByte_ · Answer

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_ · Answer

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.

Hannes Obweger - JXL for Jira · Answer

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