How to write a JQL query for items that have progressed over the past 7 days

[Felipe Fischmann]

Suppose a workflow is Backlog -> Ready for development -> In development -> Closed.

The goal is to write a query that lists items whose status has progressed over the past 7 days. There are plenty of questions regarding how to query for status changes, but the difference here is that the goal is to filter out occasional items that have moved "backwards", e.g. from "Ready for development" to "Backlog".

I hope I don't have to list the statuses one by one in the query, what I'd expect would be a concept such as `currentStatus > previousStatus`, with what's "larger" being defined by the workflow. Is there such  a thing?

[Matthias Gaiser _K15t_ · Accepted solution]

Hi @Felipe Fischmann

welcome to the Atlassian Community.

A workflow in Jira does not know about backwards and forwards, there is no clear direction in it. Depending on the workflow, there could be a multitude of ways issues can take. Therefore JQL does not have a feature to filter issues that "progressed" towards a certain goal.

I can see two possible ways, you could either filter for all issues which status changed after a certain date:

status CHANGED after -7d

or you list all the statuses to be more exact:

(status changed from "Backlog" to "Ready for development" after -7d) or (status changed from "Ready for development" to "In development" after -7d) or (status changed from "In development" to "Closed" after -7d)

Hope this gives you some insights,

Matthias.

[Felipe Fischmann]

Hi @Matthias Gaiser _K15t_ 

Thank you for your answer - even though I'd hope the functionality would be there, it does answer the question.

Although I understand that issues can go in several ways, I think there's at least a general understanding of "To do" -> "Doing" -> "Done", so it would be useful to track at least when the status of a task has progressed along this higher-level sequence. But my guess is that this isn't possible either, right? 

[Matthias Gaiser _K15t_]

Your reply got me actually thinking.

Every status in Jira belongs to a status category. The values of these categories are fixed to 'Open', 'In Progress' and 'Done' which is in essence what you've described with 'To do', 'Doing' and 'Done'.

You can also search by statusCategory or statusCategoryChangedDate. However, these don't support the history searches using 'CHANGED' or 'WAS'. So, you could add a search like:

statusCategoryChangedDate > -7d and statusCategory = Done

This would return you all issues which changed their status category in the last 7 days and are now in the Done status category.

I'm not sure how helpful this is to your problem - but maybe it helps you a little bit further 😏

[Jack Brickey]

Hi @Felipe Fischmann , welcome to the community.

While the following JQL query will identify any issues that have changed statuses in the last seven days it will not limit the results to issues that have moved forward in the workflow. There isn't a solution for this out of the box and I don't know of any marketplace addon that would do this either.

The challenge is in a defining what forward progression means within a workflow. Granted if you have a very strict workflow with singular transitions in and out of every status you could define forward progress more easily, however, most workflows do not work like that.

I can imagine a solution that you could construct leveraging custom fields and automation whereby every transition would be analyzed by an automation rule which would identify forward progression. However, this would be quite complex and require a lot of ongoing maintenance to keep it accurate.

status changed AFTER startOfday(-7)