Smart Filters for Time In Status

[Darrell]

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"

[Bill Sheboy]

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

[Darrell]

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 :)

[Bill Sheboy]

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

[Darrell]

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.

[Bill Sheboy]

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

[Darrell]

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.

[Bill Sheboy]

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