JQL to show when an issue moved into a particular status

Reid Bourgeois November 3, 2021

I would like to create a "filtered results" gadget on a dashboard that displays the date when an issue was moved into a status. 

 

For example, it would preferably look like:

Issue Key | To Do          | In Progress  | Done

XYZ-1      | 2021-11-01 | 2021-11-02 | 2021-11-03 

XYZ-2      | 2021-11-01 | 2021-11-05 | 2021-11-09

XYZ-2      | 2021-11-02 | 2021-11-04 | 2021-11-05

 

Is there a way to do this without automation updating a date field when an issue transitions? I would like to have this gadget for over a dozen projects where historical data is essential. 

 

5 answers

1 vote
Tom Walker June 15, 2023

This can be done in JQL on Jira cloud so long as you're not using a next gen project.

Do something like this:
project = "PROJ" AND status changed to Done during ("2023-04-01", "2023-07-01")

joseph_black March 29, 2024

Look for the changed operator on the following link. I think its what you are looking for

https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-operators-reference-939938745.html

1 vote
Yuliia_Borivets__SaaSJet_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 4, 2021

Hi @Reid Bourgeois 

Unfortunately, JQL can't be implemented in this case.

If you're comfortable with solutions from the Marketplace, check out Time in Status for Jira Cloud app developed by my team. You can get the date when an issue moved into a particular status with the Status Entrance Date report. It looks like this:

Status entrance report.jpg

Let me know if it helps or if you have any questions.

Kind regards,

Yuliia

0 votes
Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 9, 2021

Hi @Reid Bourgeois ,

 

Unfortunately, it is not possible with the built-in JQL functionalities. However, the data you need is available in issue history and you can get it using the issue REST API. Then you need coding to parse the json response and get the exact date for each issue.
If you are looking for a ready-made solution instead of dealing with Json API response coding and parsing, you can try Status Time app developed by our team. It provides reports on when status changes happened as well as how much time passed in each status.

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.
stf-issue-screen-view-2.png
0 votes
Rahul_RVS_Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
November 4, 2021

Hi @Reid Bourgeois 

If you would be interested in a ready made solution to view the status transition dates, you can try out our app

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 in status reports
  • Status grouping
  • Save your reports
  • CSV Export
  • Multiple chart types
  • Dashboard Gadget

 

TIS- Status Transition Dates.PNG

0 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.
November 3, 2021

@Reid Bourgeois The status changes are stored in Jira but there is not a way in JQL to get them out in the format you are asking for.  You would really need a more robust data reporting app like EazyBI to compile a report with that information.  

Gerda Grantiņa
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 4, 2021

Thanks for the mention @Brant Schroeder !
Yes, eazyBI offers an issue change history analysis where it is possible to see when the first or last transition to the status happened and how many days the issue has spent in status.
eazyBI.png
Here are two reports from the eazyBI demo account where you can see:
Status changes for issues in progress
https://eazybi.com/accounts/1000/cubes/Issues/reports/144357-status-changes-for-issues-in-progress

Issue days in selected status:

https://eazybi.com/accounts/1000/cubes/Issues/reports/787692-issue-days-in-selected-status-updated

Gerda // support@eazyBI.com

Suggest an answer

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

Atlassian Community Events