Is it possible to get the status of a ticket as of a specific date?

Chris Padilla April 26, 2021

Hello,

Each month I run various metrics on our Jira project (velocity, status, request types, etc.).  I have been asked to compare our YTD to previous quarter.  However, if I don't think to download a CSV snapshot on the first day of each quarter, then my data is inaccurate.

Specifically, is there a way to see the exact status of a ticket on a specific date?

For example, I realized the other day I didn't take the snapshot for the end of Q1.  

I can run this search to see which tickets were open as of a specific date:

project = JP AND issuetype = Epic AND status was not in (Closed) on ("2021/03/31")

However, the status reflected for each record returned is the CURRENT status and not the status as of the date specified.  In addition, I noticed today that this query above is actually including 3 tickets that were created AFTER the specified date.

 

2 answers

2 votes
Bill Sheboy
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.
April 26, 2021

Hi @Chris Padilla  -- Welcome to the Atlassian Community!

Please try something like this, substituting your project name, in progress status list, and date range

project = myProject
AND status WAS IN ("Development", "Validation")
DURING ("2021-01-01", "2021-01-31")
ORDER BY Key

You could also invert this with: status WAS NOT IN (...

To learn more about advanced JQL syntax, please look here:

https://support.atlassian.com/jira-software-cloud/docs/use-advanced-search-with-jira-query-language-jql/

 

Best regards,

Bill

Chris Padilla April 26, 2021


Bill,

Thanks for the prompt response, however, I am accessing Jira via the browser interface, not the actual backend.

Thanks,
Chris

Bill Sheboy
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.
April 26, 2021

Hi, Chris.  I was suggesting to use the browser-based, filter search.

From your top-navigation bar, select Filters > Advanced Issue Search.

You may then use the basic search, with drop-down values, or select Advanced to use the query example I provided.  When you find a search useful enough to re-use, consider saving it with a name.

The results may be viewed, exported, etc.

Chris Padilla April 26, 2021

Thanks again, Bill.

I understand now.  The way you structured the query took me back to SQL / Oracle days.

I'll give it a shot, but I suspect that what will happen is that the actual status displayed in the search results will be the current status rather than the status as of the date specified.

Chris Padilla April 26, 2021

Confirmed the behavior is as I was afraid.  

There is a ticket that shows up in the search results with a status of 'Closed' because that is the current status, but that was not the status of that ticket on the date specified.

Bill Sheboy
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.
April 26, 2021

Chris, would you please post the query you are trying, and describe what is/is not working?  That may help provide context as I was suggesting a query, and you now appear to have one matching your conditions. 

And, do you know if you have only Company-Managed (classic), Team-Managed (next-gen) or both types of projects in use in your instance?

Thanks!

Chris Padilla April 26, 2021

Thanks, Bill,

I used your query above, but put in my project name, all the states I cared about and the date range.

 

The query does return a list of results, so the query itself works.  However, the status shown in the list of results is the status as it is right now whereas I need it to print out the status as it was on the date specified.

For example, if I have a ticket where the status was "New" on 3/31/21 and it's "Closed" today, then when I run the search above to find tickets from 3/31/21, I'll see "Closed", not "New".

 

I think I may have to take a step back and change my approach using this method.  I'll give it some thought. 

 

As for the type of projects in our instance, I have no clue.

 

Thanks again!

Chris

Like Clément Fiorio likes this
Bill Sheboy
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.
April 26, 2021

Ah, now I understand after I re-read your original post. Sorry if I caused confusion.

With out-of-the-box Jira, you cannot do that.  You are essentially describing "show me the issues meeting this criteria at a moment in time".  It is the moment part that is the challenge.  I believe there are marketplace add-ons for purchase that can do this...but if you pause to consider the problem you are solving, that may not be needed.

For example, you care about issues in a specific status during a period of time.  You could write a specific query (as noted above) for just that status.  It doesn't matter what the status is now; you know what the status was then.  If you wonder about multiple status values, you could create a query for each status.  When done, merge the results. 

One challenge with this method is that the same individual issue could have been in multiple statuses within the same time range.  Again, the problem you are trying to solve will guide what to do in that situation.

Chris Padilla April 27, 2021

Yes, that's exactly what I was thinking.  It's a little unhandy as my particular Jira project has so many different states for tickets that I would need many different queries to run to capture all the state changes I want to report on.


Thanks!

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

Hi @Chris Padilla ,

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

stf-issue-screen-view-2.png

Suggest an answer

Log in or Sign up to answer