How to show the age of tickets in some status with help of jira gadgets?

[Marcel Sarvaš]

Hi,

I'm working on setting up a board in Jira with a gadget that can display the "age" of tickets in a specific status. In my case, I want to track the time spent in the QA column, but I prefer not to use any third-party tools, only the gadgets available in the Jira dashboards.

I want to display the duration for which certain tickets have been in the QA column without their status being changed. I have come across several similar questions here, but haven't found a solution that addresses my specific issue...

Here is what I prepared:

- all tickets in QA: 

status = UAT

- 0-2d in QA: 

project = “QA” AND status = UAT and NOT Status changed during ((now(),startOfDay(-2))) and status was not "UAT" DURING (startOfDay(-3), endOfDay(-3))

- 2-5d in QA:

project = “QA” AND status = UAT and NOT Status changed during (endOfDay(-3), startOfDay(-5)) and status was not "UAT" DURING (startOfDay(-6), endOfDay(-6))

- 5-10 in QA:

project = “QA” AND status = UAT and NOT Status changed during (endOfDay(-6), startOfDay(-10)) and status was not "UAT" DURING (startOfDay(-11), endOfDay(-11))

- 10d+ in QA:

project = “QA” and status = UAT and NOT Status Changed to UAT AFTER -10d

I'm having trouble with the numbers I'm seeing in each section. Sometimes the issue is with the second filter, and other times it's with the third one, and so on. The first filter, which summarizes tickets in the QA column, shows the correct number of tickets. However, when I add up all the other filters, the total is always higher. It seems like some tickets are being counted multiple times.

Is there any problem with the filters? Should I use a different JQL? Or how can I achieve this task, to show how many days are tickets present in one status without making a status change? 

[Marcel Sarvaš · Accepted solution]

Thank you all, I will be trying out the Bloompeak app.

[Mehmet A _Bloompeak_ · Accepted solution]

Hi @Marcel Sarvaš

If you decide to try a marketplace app, you can try Status Time Reports Free app developed by our team. It is completely free and the limited version of Status Time Reports app.

This app mainly provides reports and gadgets based on how much time passed in each status. It also has a dynamic status grouping feature so that you can generate various valuable reports as cycle time and lead time, time in status, time in assignee, status entry dates and status counts, resolution time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

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 Cycle Time and Lead Time in Jira: Productivity Measurement with Two Critical Parameters article and Status Time Reports How to Videos.

App Features:

• 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 have any questions, feel free to schedule a demo with us.

Hope it helps.

[Valeriia_Havrylenko_SaaSJet]

Hi @Marcel Sarvaš 👋

Check out this tread. Ste Wright, community leader of Atlassian Community, found a workaround that might be useful for you 
https://community.atlassian.com/t5/Jira-questions/Jira-Gadget-for-Open-Issues-Age/qaq-p/1457702

Also this article can be useful for you:
How to use automation to get the time an issue was in a status

If you ever consider using an app from the Atlassian Marketplace, you might want to check out Time in Status. Add-on is available both for Cloud and Data Center. 

You can visualize the aging of tickets according to the no. Issue Age Data available by Table view, Chart view and Dashboards. 

I hope you find this helpful 🚀 

[Amay Purohit_RVS]

Hi @Marcel Sarvaš 

If you are open to try a mktplace app in future, pls do take a look at 

Time in Status Reports 

With this app you generate time in status for multiple issues with multiple filter and grouping options. With the ability to export to a csv file as well for further analysis as shown below.

Also the app has 20+ reports to meet a variety of time in status use cases. Do give it a try.

Disclaimer : I work for the vendor who built this app

[Danut M _StonikByte_]

Hi @Marcel Sarvaš,

I'm afraid that it's not possible to achieve this just by using filters/JQLs, or by using Jira's native gadgets. Most likely, you will need a 3rd party add-on. 

A good option here would be to use the WIP Aging Chart gadget offered by our Great Gadgets app. This gadget displays the issues from a filer or board by their age (time in their current status or in the current board column (like QA column), or time since issue creation), exactly as you want.

 

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

On its Data tab, it displays more details about the issues and their age and you can export this in CSV.

The app requires a payed subscription, but you could give it a try by starting the 1-month free trial. For any questions, please don't hesitate to contact support@stonikbyte.com.  

Danut.  

[Hannes Obweger - JXL for Jira]

Hi @Marcel Sarvaš

for a native solution, you might be able to calculate the time in a certain status using Jira Automation, writing the result into custom fields, and then using these custom fields in your reports. This article might be a good starting point.

If an app from the Atlassian Marketplace should become an option for you, 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 long list of so-called history columns that aren’t natively available, including time in [status], time between [status] and [status], date of [status change], and many more.

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.

Of course, you can also export your data to XLSX (Excel/Google Sheets) or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes

[Bill Sheboy]

Hi @Marcel Sarvaš 

Some challenges using the CHANGED operator for your scenario are: getting the syntax correct, handling overlaps with those date / time functions, time zone handling, and accepting prior time in the status could cause errors: https://support.atlassian.com/jira-software-cloud/docs/jql-operators/#CHANGED

Plus there are known defects with CHANGED and issue changelogs, particularly if there are now (or ever have been) any team-managed projects used in the site.

Without using marketplace addons, some options to try are:

• adjusting your JQL to use the BEFORE and AFTER operators rather than DURING to take better control of time frame overlaps
• using an automation rule and a custom field to capture the current time in that status (or the start date / time of entry), and test that field with JQL

Kind regards,
Bill

[Gizem Gökçe _OBSS_]

Hello @Marcel Sarvaš ,

I understand that you are not looking for a 3rd party app at the moment, but it is always safe and easy to use a marketplace app to track the time spent on each status. So just in case you ever consider using an app, I will recommend one for you. 

Timepiece - Time in Status for Jira ,the oldest and leading "Time in Status" app in Atlassian Marketplace, 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. Our app has a 30 day free trial and it is free up to 10 users. You are welcome to download and try it.

Timepiece 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. You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

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.

Timepiece 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. 

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