DaysInColumn in JQL

[JD Lobue]

Is there any way to pull into a Filter the Days in Column feature that is on the cards on the Kanban board? It sure would be great to be able to use that feature for filters.

If there isn't, what do others suggest to be able to call out high cycle times during sprints to the team other than the card itself. I'd like to hit a quick filter and show all cards w/ high cycle time.

[Mariana_Pryshliak_Saasjet]

Hello @JD Lobue 

You can also try Time in Status for Jira Cloud, which generates a report on how long an issue has been spent in each status. 

This add-on shows Cycle time in a few clicks, and you can export it as XLSX or CSV files for future analysis. You will be able to create multiple calendars to exclude breaks or non-working time.

If you have any additional questions, I'm here to help you:)

Best regards, Mariana

[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 they 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.

[Bloompeak Support]

Hi @JD Lobue ,

As an alternative you can try Status Time app developed by our team. It provides reports on how much time passed in each status as well as status entry dates and status transition count. Report can be exported as CSV, so that you can use external tools like Microsoft Excel or Google Spreadsheet to process the exported data.

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(eg. average in progress time per project).

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

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

[Sudarshan]

Hello @JD Lobue 

To my understanding the Days in feature on the cards is a built in feature and works only on the cards and may help in creating the cycletime reports.

I tried some thinking and can suggest the below points:

1. use the datecompare function (whih can compare the values of any 2 datepicker fields
   

   project = XYZ AND issueFunction in dateCompare("", "Created = Created")

   This will give me the list of items created and Updated on the same day (only basd on date not time).
   you can also try it with other date related functions (the above is a simple example to explain the JQL)
2. the cycletime chart can give you the outliers effectively (Jira Control Chart)
3. Check out the bubble chart gadget with a current sprint filter (if u use scrum) for Kanban just create a filter with the required status. (refer)

I recommend the bubble chart as it shows visually based on your custom filter.

• number of days the issues have been open
• number of comments the issues have
• number of participants or votes the issues have

I will try to provide a Quick filter soon.!

[JD Lobue]

I look forward to a way to have a quick filter soon. Thanks again.

[Darryl Lee]

Oooh, if you're using issueFunction, that means that you have ScriptRunner. (That's an add-on, FYI.)

In which case, would it be easier to create a Scripted Field that calculates Time in Status? (I'm still searching to see if that's possible. :-)

[Darryl Lee]

Drat - I don't think Scripted Fields will work:

Scripted fields in Jira Cloud do not dynamically update. The script triggers on issue load; therefore, changes to the field value are not reflected instantly, the issue must be reloaded.

And also:

Currently, scripted fields in Cloud cannot be used as part of a JQL query.

https://scriptrunner-docs.connect.adaptavist.com/jiracloud/scripted-fields.html

I was also thought about creating a custom field and using Automation to calculate time in status and fill it, but yeah, that wouldn't dynamically update. It could only be triggered when the status changes, so then, time in last status, which isn't right.

Time In Status is a great add-on, but you'll have to accept that the UI is not the same as the built-in Filters.

[Emre Toptancı _OBSS_]

Hello @JD Lobue ,

Jira does not provide this out of the box.

If you are using Jira Software, Control chart in Kanban Board reports offer some visibility into this. It works with board columns rather that individual statuses and shows issues in a grouped chart rather than as individual issues. Custom calendar support is also limited but it can help you to get a high level report.

Alternatively, you need to do your own custom development. Since you are on cloud, you can't access the database so the option for you to get issue history data will be through REST API.

Beyond that, you can use a marketplace app to get the report you need.

For a ready built solution that offers great flexibility and details, our team at OBSS built Time in Status app for this exact need. 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 or assigned to each assignee. You can also combine statuses into consolidated columns to see metrics like Ticket Age, Cycle Time or Lead Time. You can calculate averages and sums of those durations grouped by issue fields you select. (For example see the total InProgress time per Epic, or average Cycle Time per issuetype). 

     

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.

The app has Custom calendar support that your are looking for. This feature enables you to get your reports based on a 24/7 calendar or your custom business calendar. (This one is important because a 24/7 calendar in most cases shows misleading data. For example an issue created at 16:00 on Friday and was resolved at 09:00 on next Monday seems to stay open for 2,5 days but in terms of business hours, it is only a few hours. You can see this using Time in Status by OBSS.)

Finally, the app has History Trim feature. This feature allows you to report on a subsection of issue histories instead of the whole history. Very useful when you want to focus on changes made during sprint or project phase.

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 issuetype.)
• 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 (released for cloud, server&DC gadget coming soon)

https://marketplace.atlassian.com/1211756

EmreT

[JD Lobue]

I'll take a look at your Time in Status plugin. Thanks