Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Smart Filters for Time In Status

I am trying to capture how long issues have been in progress, and are still in the status of "In Progress", are in using smart filters. It seems like the query I'm using is capturing how long an individual issue has been in Progress. However, it also seems like every time I move an issue to another status it receives another instance of what it's capturing.

 

For example, I moved an issue that has had the status "In Progress" for 2.5 weeks to "On Hold". When I move it back to In Progress that issue is represented both in the 2.5 weeks range and the < 1 week range since it was just moved back to In Progress.

I'm not able to add any additional plugins but I do have REST API access so if there's a way for me to script this by hand I'm open to trying that too.

Smart Filters:

 

Label: < 1 Week

Filter: status changed to "In Progress" after -7d AND status = "In Progress"

Label: 1-2 Weeks

Filter: status changed to "In Progress" after -14d AND status changed to "In Progress" before -6d AND status = "In Progress"

Label: 2-3 Weeks

Filter: status changed to "In Progress" after -21d AND status changed to "In Progress" before -13d AND status = "In Progress"

Label: 3-4 Weeks

Filter: status changed to "In Progress" after -28d AND status changed to "In Progress" before -20d AND status = "In Progress"

 

Label: > 4 Weeks

Filter: status changed to "In Progress" before -28d and status = "In Progress"

1 answer

Hi @Darrell  -- Welcome to the Atlassian Community!

In your queries you may have some overlapping timeframes and logical conditions.  For example, your < 1 week and 1-2 week cases overlap on -7d. My suggestion would be:

  1. Hand draw a time line and mark on it what you want to know
  2. Identify some example cases of issues meeting your criteria of "what you want to know"
  3. Review the time line to determine if there can be overlaps for those cases
  4. Iterate steps 1-4 until your cases are distinct
  5. Now write your queries to match


Best regards,

Bill

I will adjust this and see what happens. Are the before and after predicates inclusive or exclusive for the days I'm specifying? 

Edit: Thank you for the warm welcome :)

I believe they use the start of day, which is more relevant for the date/time fields.  The before and after operators should be exclusive. DURING may help more for your examples.  I am using cloud not server, so please experiment to confirm.  Here is the documentation reference on this to help you:

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

Using During was easier to map visualize than attempting Before and After so thank you for helping me with fixing the overlap!

New Filters:

< 1 Week: status changed to "In Progress" after -7d and status = "In Progress"

1-2 Weeks: status changed to "In Progress" during(-14d,-7d) and status = "In Progress"

2-3 Weeks: status changed to "In Progress" during(-21d,-15d) and status = "In Progress"

3-4 Weeks: status changed to "In Progress" during(-28d,-22d) and status = "In Progress"

>4 Weeks: status changed to "In Progress" before -28d and status = "In Progress"


However, this doesn't fix the issue of Jira counting my issues twice because of them having multiple instances of being moved to In Progress.

One issue was moved to In Progress on May 24th and May 4th. The issue is found in the 1-2 Weeks Column and the 3-4 Weeks column. This same issue should move from the 3-4 Weeks to > 4 Weeks tomorrow. I need to track the total # of days it has been In Progress and not the individual stretches of time of it being In Progress.

Hi, Darrell!

Yup, that seems correct if issues are moving forwards, backwards, and forwards again in the process steps...  Let's pause for a minute and consider what level of detail you need.

How complex are your process steps?  If you have a relatively simple process (or only care about "in progress" regardless of any sub-steps/status values), a simplification is to use statusCategory.  That field summarizes all statuses ("To Do", "In Progress", and "Done") and has a helpful related field: statusCategoryChangedDate.  With those you could do this:

< 1w: 
project = myProject
AND statusCategory = "In Progress"
AND statusCategoryChangedDate > -7d

1-2w:
project = myProject
AND statusCategory = "In Progress"
AND statusCategoryChangedDate <= -7d
AND statusCategoryChangedDate > -14d

and so forth...

This approach has a limitation: if you move In Progress issues to Done and back to in progress, the clock restarts for statusCategoryChangedDate.  Same issue for moving back to the backlog.

When you need more details by individual status values, you cannot easily do that with JQL.  Instead you would need some marketplace addon for JQL or time/status measures, or you could build your own with automation rules and custom fields.

Best regards,

Bill

I only care about In Progress, regardless of sub-steps, and statusCategory is incorporating more statuses than I want.

I mentioned in my post that I do not have access to plug-ins but I do have REST API access so guidance towards the beginning of a custom solution is what I'm looking for.

Thanks for clarifying what you are able (or not able) to try given the workflow and tooling limitations for your Jira instance.

Please try searching in this community for REST API change history to find posts about using those methods.  You may also find information in the developer community on this: https://community.developer.atlassian.com/

If you have direct access to your Jira server database, please take a look at this knowledge base article: https://confluence.atlassian.com/jirakb/retrieve-issue-change-history-from-database-in-jira-server-933695139.html

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

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

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you