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.
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:
Best regards,
Bill
Bill,
Thanks for the prompt response, however, I am accessing Jira via the browser interface, not the actual backend.
Thanks,
Chris
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.