JQL query to show "days in column" for issues

[Juan Manuel Ramos Taboada]

Hi Community,

I am trying to find a JQL filter to display issues sorted by "days in column". It is my understanding that this is not a field per se, but an option in the "Card layout" section of the Board Settings.

The idea is to show which issues and how much time they spend in the "To Do" Kanban column.

Can somebody help me, please? :)

[Jonas Bang Christensen]

Based on the solution in this topic I have created a Quick Filter to show "issues in same column for x days".

Simply add this into the JQL:

NOT status changed after -5d

Or:

NOT status changed after -1w

I also hide issues in "backlog" columns for this Quick Filter as issues are allowed to stay in same column for a long time, so:

NOT status changed after -5d AND status not in ("In Refinement", "Approved Backlog", "Done")

I hope this can be useful.

[Ewa Wojcikowska-Staniak]

Hi, is this exactly the jql language? I tried to configure card colour based on this jql and it does not seem to work - thanks!

[Lucy Warner_SaaSJet]

Hello @Juan Manuel Ramos Taboada 

If you're OK with using third-party add-ons, I recommend you to try Time in Status for Jira Cloud. 

You don't need to enter the add-on to see the time in status for each issue. All you need is to set up Custom Fields and present data on the issue card layout.

IBut also you can display data on the grid or charts view ( youcan choose what is more convenient for you) and then export data to CSV or XLSX files for further analysis

This add-on is developed by my team and you can get a free 30-day trial version or ask me any questions you have

Hope it helps.

[Hannes Obweger - JXL for Jira]

Hi @Juan Manuel Ramos Taboada

if you're open to solutions from the Atlassian Marketplace, you might like 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 "smart columns" that aren't natively available, including the time since status changed.

This is how it looks in action:

As you can see above, you can easily sort and filter by the time since status changed. You can also view it in different formats (like number of days, or number of hours), and use it across JXL's advanced features, including support for configurable issue hierarchies, issue grouping, sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

[Valerie Knapp]

Hi @Juan Manuel Ramos Taboada , thanks for your question.

Have you tried checking out some of the reports present in the project?

I think the Control Chart or the Cumulative Flow Diagram might already show something similar to what you are asking.

For example, the Cumulative Flow Diagram shows the number of issues per status over time -

I hope this might be useful but if it's not what you're looking for, please give your feedback and either I or someone else will try to help.

Cheers

[Stephen George]

Ultimately, I think that Valerie's suggestion is best of what's offered in this thread so far. The Cumulative Flow Diagram, as she suggests, will not only show you periods of time where the most tickets were concurrently in the To Do state, it will also allow you to hover and see specific ticket IDs.

But I'm going to try my best to answer your original question with JQL as requested.

SPOILER: I was only able to arrive at some approximate solutions. But nevertheless I hope these are helpful in some way.

I am trying to find a JQL filter to display issues sorted by "days in column".

While not perfect, a close-approximation for `Status` changes is a general `statusCategory` change.  As you may already know, while there are many possible statuses in the default workflow, each maps to just 1 of 3 possible `statusCategory`s: "To Do", "In Progress" or "Done".  

And fortunately you can sort by a related field, `statusCategoryChangedDate`, to find tickets that have been in the same `statusCategory` for the longest period of time:

ORDER BY statusCategoryChangedDate ASC

Limitation: Sadly you can see that the above option doesn't fully address your question because, depending on the `Status` transition, a ticket can move from column-to-column and still remain in the same `statusCategory`.

The idea is to show which issues and how much time they spend in the "To Do" Kanban column.

One quick & dirty way you can look at all tickets that began & ended the previous month in the "To Do" column would be with a query like this using the WAS operator:

status WAS "Open (Todo)" BEFORE startOfMonth(-1) AND status WAS "Open (Todo)" AFTER endOfMonth(-1)

 This might be suitable if you're always looking at the past month, for example; but not suitable for a full overview over all of time.

[Madhu_RVS]

Hi @Juan Manuel Ramos Taboada 

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

Time in Status Reports 

The add-on provides the time in status report for each issue in your filter. There are multiple other reports which help in tracking the complete life cycle of the issues.

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

[Mehmet A _Bloompeak_]

Hi @Juan Manuel Ramos Taboada

As an alternative, 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.

• 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, 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 free solution, you can try the limited version Status Time Free.

Hope it helps.

[Emre Toptancı _OBSS_]

Hello @Juan Manuel Ramos Taboada ,

Jira does not have an out-of-the-box report that shows individual status times. If you are interested in overall resolution times, Issue Age Report or Control Chart (of Kanban Boards) might be useful up to a degree. It is worth noting that these options offer limited details and limited flexibility.

JQL by itself only can filter and sort issues based on issue fields but as you pointed out, this data is not available on the issues as a field so JQL is of no help here.

In fact, the raw data needed to create such a report is available in the History tab of each issue but it is hard to turn these into a ready-to-use report.

For a ready-to-use report, the use of a marketplace app is needed.

If you are OK with using a marketplace app for this, our team at OBSS built Time in Status for this exact need. 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. The app also allows the user to filter and sort issues based on these times.

 

The app has Consolidated Columns feature. This feature allows you to combine the duration for multiple statuses into a single column and exclude unwanted ones. It is the most flexible way to get any measurement you might want. Measurements like Issue Age, Cycle Time, Lead Time, Resolution Time etc. Filter and Sort options are also available for Consolidated Columns.

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 (organization) 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. (Filter and Sort options are even available here.)

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.

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.

  

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

EmreT