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.
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:
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.
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, 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?
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.
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.
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event