Filter to fetch the list of tickets which got stuck in a status for more than 3 days(exc. weekends)

Question

I want to set up a filter so that I can get a list of tickets which are stuck on a particular status for more than 3 days. The filter which I'm using currently is including weekends too. Let's say, the status is changed on friday, and the filter is ran on Monday, then it shows the ticket in the list but ideally it should not be the case since we don't want to count in Saturday and Sunday. The filter I'm currently using is - issuetype = "Change Request" and status = "PM Review" and status changed before -3d

It would be great if someone helps finding out the correct filter.

Thanks

Chellaram_H · Answer

Hi @Mayank Agarwal

I believe you will have to use Jira Rest API's to pull the issues changelog data and filter the same for your requirements. An easy way would be to use an app from the mktplace.

If you are fine to try out a mktplace app for this use case, take a look at

Agile Tools

The time in status report in our app works on the calendar settings provided by you. Also you can further filter it down for any status duration more than x number of days.

Do try it out.

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

Emre Toptancı [OBSS] · Answer

Hello @Mayank Agarwal ,

There is no way to do this with Jira's built-in JQL queries because JQL does not care about weekends or weekdays. You'll need some kind of marketplace app that considers your custom calendars.

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

Time in Status supports custom calendars (each calendar with its own working days, working hours and holidays). It allows you to sort or filter issues based on these metrics. You can easily get a report that shows "the issues that stayed in 'PM Review' for more than 3 days according to my work calendar".

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.

Timepiece - Time in Status for Jira

EmreT

Danut M _StonikByte_ · Answer

Hi @Mayank Agarwal,

A better option here, would be the WIP Aging Chart gadget offered by our Great Gadgets app.

This gadget can display the issue from a filter or board in a plot chart based on their age (time in current status). You can also specify a threshold to easily identify the issues that got stuck in their current status (the ones in red). It has an option to exclude weekends.

See this as an example.

Hovering an issue in the chart, will display the age of that issue along with some other details (Key, Summary, etc) in a tool tip.

This app offers many other gadgets that you will find useful. Have a look over our blog to see how many things you can track with our app.

I hope this helps.

Danut.