JQL to identify the cycle time of an item

[Bob Blakey]

I am wanting to identify the age of an item that has started the Status of "In Progress" but not been fully "Done".  So I would like to start a "timer" once a ticket is pulled from the backlog and worked all the way till it is fully tested and placed in the "Done" column.  I know about the column age indicator, but that isn't tracked across column.  I would like to use the query capability in the card color section of the board to highlight tickets that are nearing a certain age.

Any help is apprecated.

Thanks

[Walter Buggenhout · Accepted solution]

Hi @Bob Blakey,

Jira does provide cycle time analysis in the Control Chart report.  You can read more about that in this support article.

It is not an exact answer to your question though, as you need to navigate to reports and as such away from your board to access the information. Still, very powerful stuff.

If you want to set card colours on the board, you'll have to make some tradeoffs. Let's assume you have a decent workflow that encourages you to move tickets from left to right through the board, then you can use the a simple set of conditions like this:

Resolution = Unresolved AND Status changed to "In Progress" BEFORE -5d (card colour -> yellow)

Resolution = Unresolved AND Status changed to "In Progress" BEFORE -10d (card colour -> orange) 

Resolution = Unresolved AND Status changed to "In Progress" BEFORE -15d (card colour -> red)

The Resolution = Unresolved part is optional. But apart from that, it is only a matter of playing with the amount of days you want to use to define colours and putting your different rules in the right order.

Hope this helps!

[Bob Blakey]

This looks like it works exactly the way I want.  I will play around with it, thank you.

[Zoryana Bohutska _SaaSJet_]

Hi @Bob Blakey 

You can read more about tools that allow calculating cycle and lead time without JQL here 

https://community.atlassian.com/t5/Marketplace-Apps-Integrations/3-tools-to-analyze-Cycle-and-Lead-Time-in-the-Jira/ba-p/1403775 

[Rahul_RVS_Support]

Hi @Bob Blakey 

If you would be interested in a readymade solution, you can try out our plugin,

Time in Status Reports 

The add-on provides the time in each status for the entire lifecycle of the issue and you can filter by issue type as well. You can also combine your statuses to define your lead/cycle/resolution time and also extract the transitions history of the issues. The main features of the app are as below

• More than 8 types of Time in Status Reports. Excel Export available for all status reports.
  
  • Time in Status
  • Time with Assignee
  • Time in Status with Assignee
  • Time with Assignee per Status
  • Status/Assignee Count
  • Multiple Transition Reports
  • Avg Time reports
• Status grouping
• Save your reports
• CSV Export
• Multiple chart types

[Emre Toptancı _OBSS_]

Hello @Bob Blakey

Control Chart of Kanban boards can be used for this purpose or at least some part of it. It offers limited transparency about its inner workings and has very limited flexibility but can help you get a very good overall picture.

If you need more details and more flexibility, our team at OBSS built Time in Status app for this exact need and more. It is available for Jira Server, Cloud, and Data Center.

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

 

You can calculate averages and sums of those durations grouped by the issue fields you select. For example average resolution time per week, month, issuetype 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.

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.

 

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets

https://marketplace.atlassian.com/apps/1211756/

EmreT

[Bloompeak Support]

Hi @Bob Blakey,

Unfortunately, what you are looking for is not possible with the built-in functionalities of JQL. Because JQL can not make calculations and you need status time calculation.

For this exact need we developed Status Time Jira app. It provides reports on how much time passed in each status. By grouping statuses you can get cycle time(E.g from in progress status to done status). You can also export the report as CSV and open it in excel.

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. It has various other reports like assignee time, status entry dates, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month). And all these are available as gadgets on the dashboard too.

Here is the online demo link, you can see it in action and try without installing the app.

If you are looking for a free solution, you can try the limited version Status Time Free. Hope it helps.

[Bill Sheboy]

Hi @Bob Blakey 

I can think of three (+) ways to do this:

• If you have a specific threshold timeframe in mind, try the status category to check this.  Here is an example to show issues that are "in progress" for at least 2 days.

project = myProject AND issueType IN (Story, Task, Bug) AND statusCategory = "In Progress" AND statusCategoryChangedDate < -2d

• Another way to do this is to use a custom field and an automation rule with a scheduled trigger to compute the time in progress, and build a filter based upon that.  That would give you a number for each issue.  Here is an example to compute the Age of WIP.
• If you want better reporting and are okay not seeing this on the board, you could:
  • Take a look at the built-in control chart
  • Look at the marketplace for addon apps for purchase

Kind regards,
Bill