How can I write a JQL filter that shows status change date?

SHEHZADALI ISMAILY January 5, 2023

How can I write a JQL filter that shows me the date of when an issue last transitioned from any status to any another status? and when it transitions again, the new updated date of that status transition shows.

7 answers

3 votes
Brant Schroeder
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 5, 2023

@SHEHZADALI ISMAILY This information is stored in the history of each issue and you can view a specific issue to see when the transition occurred.  You can use JQL to see if a transition occurred during a specific time period as well but it will not give you the details you are after.  

STATUS Changed AFTER '2022-12-01 10:00' BEFORE '2022-12-01 12:00' TO Approved BY username

 There are apps in the Atlassian Marketplace that can also be purchased that provide this information I believe time in status would work but there are also others.

Artur Balyan @ Immedis April 4, 2024

I was looking for this. Here is how it worked for me:

and STATUS Changed AFTER "2024-03-01"
and STATUS Changed BEFORE "2024-03-31"

3 votes
Olga Videc
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 5, 2023

Hello @SHEHZADALI ISMAILY 

Unfortunately this cant be achieved out of the box you need to use an add-on.

But there is a free basic add-on that could meet your requirements called Status Time Free | Atlassian Marketplace i

I tested it quick as far as I can see it fulfils your requirements

Check

Report.PNG

BR, Olga

2 votes
Benjamin
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 5, 2023

Hi @SHEHZADALI ISMAILY ,

 

That is not possible. You can only see the transition date in the issue itself. However, you can create a JQL to see what was the last status it change from. If you work like to look get deeper into analysis, you might want to look at marketplace add-ons like EzBI.

 

-Ben

SHEHZADALI ISMAILY January 5, 2023

Ben, thank you for your reply. What would the JQL look like to show "the last status it changed from"?

Benjamin
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 5, 2023

Hi @SHEHZADALI ISMAILY ,

 

Something like this:

 

status = close and status was in ("In Progress")

 

For example, this filters all issues that is in status close that was in status "In Progress".

 

There is caveat is that it may not exactly show the last status depending on how the user traverse through the work flow.

The add-ons that are shared by @Olga Videc  and @Brant Schroeder  is probably worth looking into. Especially, if you are looking for something that tracks all the status timings of a issue workflow.

 

-Ben

1 vote
Hannes Obweger - JXL for Jira
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 23, 2024

Hi @SHEHZADALI ISMAILY,

if you're open to solutions from the Atlassian Marketplace, this would now be easy to do using 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 history columns that aren’t natively available, including the date of last transition to [status], date of last transition between [status] and [status], and many, many more.

This is how it looks in action:

last-transition-to-status.gif

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

This all just works - so there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes

1 vote
Rahul_RVS_Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
January 6, 2023

Hi @SHEHZADALI ISMAILY 

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 each status for the entire lifecycle of the issue, based on the date range, assignees and multiple other filters.

Also the Status Transition Dates report will  meet your use case.

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

TIS - Status Transition Dates.PNG

0 votes
Kiri Golberg
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
March 22, 2024

Hi!

0 votes
Valeriia_Havrylenko_SaaSJet
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
March 21, 2024

Hi @SHEHZADALI ISMAILY and everyone who is looking for solution 😌

As an alternative, you can try - Time in Status for Jira (Cloud, Data Center), (developed by my team) that generates 7 types of status reports including Status Entrance Date report. It shows the date a particular issue has entered each of a status (Resolved or Closed, etc.).

9e9e93ff-9254-4123-8774-817cc45b769d.png

  • set date or date ranges you need
  • get dates when the issue has entered each of the statuses on the grid.

 
On the chart, it shows the number of tasks that have entered a status on a certain date - for example, 1 task out of 5 tasks has entered the Progress status for the first time on the 23-rd of May

 5f12830b-97ce-442c-ae4c-f964e527c72c.png

Add-on has a 30-day free trial version and free up to 10 users 
Hope it helps 😌

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
TAGS
AUG Leaders

Atlassian Community Events