How to track how many times a story has been spilled over different sprints

[abhishek_sharma03]

Dear All,

We have a problem statement from a client - There is a lot of spillover in a sprint, team members keep pushing the user story to next sprints. We want to have a track of number of times an issues was pushed from sprint to sprint.  

I know we may have discussed this question multiple times in this forum of this being a process oriented problem but can we actually track this in Jira through any gadget or plugin ?

BR

Abhishek

[Jack Brickey]

Hi @abhishek_sharma03 ,

you could use Automation to update a custom field (sprint-count) using the following smartvalue

{{issue.Sprint.size}}

Given that the Sprint field is a list the above will return the number of items (sprints) in the list. Please note that if someone removes sprints from the list then the count will be off. However , you might be able to incorporate logic in your rule such that the count is not updated if the new update would result in reduction. However (again), consideration should be given to the scenario where an issue is added to a sprint during planning and then removed before the sprint starts. 

as you can see, it is a bit complex to achieve an accurate count but doable. I am sure there are addon apps available so you may wish to check the Marketplace if the above isn't a desirable solution.

[Bill Sheboy]

Hi @abhishek_sharma03 

Yes, and...to Jack's answer and your question:

You have identified this is a process / learning problem, and so please consider that when deciding how to manage this.  Key things to understand are "how often is this happening" and "why is this happening".

If you believe this behavior can be identified, managed short-term, and periodically checked upon, please consider exporting the issues to a spreadsheet, count the sprints with formulas/pivot tables, and have discussions with the team to decide improvements.  Once the behaviors change the measurement can stop.

If you believe this is a longer-term issue, you can add a custom field and use an automation rule to try to track this symptom.  There is a risk in that approach: if it becomes normal to measure this it may be perceived as "normal" that a lot of work carries over sprint after sprint.  Having conversations to understand the root cause can mitigate that, as this symptom typically comes from challenges with:

• work intake
• sprint planning and capacity planning
• team focus / high WIP during the sprint
• knowledge silos in the team
• work dependency on other teams
• build / test / release quality
• lack of support for agile practices
• etc.

Discussing with the team members will reveal how to experiment to improve.

Kind regards,
Bill

[Guy Anela]

Hi folks, I “think” I may have come up with a solution using a combination of Custom Fields, Automation, and an API call. 

I say "think" because I just released it to one of our teams to review/test so I'm not sure if it's working completely yet or not. I figured I'd post it here too so it can be hammered on by others as well. :)

As @Bill Sheboy previously mentioned, this is ultimately a process/learning problem that needs to be addressed. This solution is to only help track the symptom. Thanks for clearly pointing this out, Bill! :) 

Solution:

In this solution, I’m tracking two different “spillover” scenarios...

1. Issues that are committed to a Sprint but are not Done when the Sprint is marked Complete.
2. Issues that are removed from an “Active” Sprint. 

I added two Custom Fields:

1. A Number field named "Sprint Spillover Count" (represented by customfield_8888 in the Automation screenshots below) - It's used to track the number of times an issue is either removed from an Active Sprint or not “Done” when the Sprint is completed.
   
   
2. A Multi-Line Text field named "Sprint Spillover Data" (represented by customfield_9999 in the Automation screenshots below) - It's used to log details about the spillover.

I created two new Automations that populate the new fields:

1. The first one is triggered when a Sprint is Completed. It will cycle through all of the issues in the Sprint that aren’t Done, increment the count on the "Sprint Spillover Count" field, and log an entry in the "Sprint Spillover Data" field.
2. The second one is triggered when an issue is removed from an Active Sprint. Similarly, it will increment the count on the "Sprint Spillover Count" field and log the entry in the "Sprint Spillover Data" field. 

Automation details for the first Automation:

 

Automation details for the second Automation: 

REVISED (See my comments in THIS SUBSEQUENT REPLY)

Anyhow, I hope this helps. I'll post an update if the team finds any significant issues during their testing. Cheers! 

[Bill Sheboy]

Hi @Guy Anela 

Thanks for that suggested approach.  And as a heads up...

The changelog entries which automation rules can see for list fields like Sprint are not always accurate.  (e.g. {{fieldChange.from}} )  I recall there is open defect for this and the Fix Versions and Affects Versions fields.

For your second rule, this means it may not accurately detect the removal from the sprint scope by only checking a change to the Sprint field.  In the past, I have created a workaround for this by:

• Create a rule triggered on Sprint Started, which marks the issues in a way to confirm they were in scope at the start of the sprint (e.g., a label, custom field, or entity property).  The entity property may help prevent tampering due to its reduced visibility.
• In the rule which detects a change to the Sprint field, use a JQL condition to check if the issue is in the current sprint, and if not also check that previous indicator shows the change of scope
• In the rule triggered on Sprint Completed, after all of the math / measurement is completed, clear that indicator from all issues to prep for the next sprint

Kind regards,
Bill

[Guy Anela]

Thanks for the heads up, @Bill Sheboy

Interesting, the {{fieldChange.from}} results have been accurate during my testing (so far), but I'll definitely keep a closer eye on it now.

I did some digging; Is JRACLOUD-81160 the Bug you're referring to? If so, it looks like it may only occur if multiple values are deleted from the field (based on the example in the Bug). If that's the case, I don't think this will be a problem with Sprints, right? Either way, I'll definitely pay closer attention to it. 

Also, by the way, this opened my eyes to {{deletedFieldChange.values}} which I believe I can use in place of my For Each.  ...learn something new every day. :) 

Thanks again, Bill!

[Guy Anela]

Yep, I just confirmed that I can use {{deletedFieldChange.valueIds}}.

EDIT: I removed the example here since I posted an updated version in my original reply.

[Apryl Harris]

Thanks for this. I can see this being very helpful.

[Bill Sheboy]

Hi @Guy Anela 

Yes, that is one of the open defects.  I could not find the other one.

The symptom for the Sprint field appears to also occur because there are multiple paths to create (and assign) a sprint, and some do not consistently work (for changelog returns).  e.g., from backlog, from sprint completion, etc.

Thanks again,
Bill

[Guy Anela]

Thanks, @Bill Sherby! I've tested the following paths and I haven't seen any issues (yet)...

• Dragging/dropping the issue in/out of the Sprint from within the Backlog.
• Completing the Sprint with Open/Closed issues.
  • Rolled over to new Sprint
  • Rolled over to Backlog
• Directly editing the Sprint field.
  • Deleting the Active Sprint
  • Adding to a new Active Sprint
  • Adding to a Future Sprint

I'll continue to pay close attention to it (fingers crossed :)). 

Thanks again! ...your continued support/guidance in the Community is greatly appreciated. <3  Cheers! 

[Guy Anela]

I ran into a snag with the Automation that gets Triggered on the Sprint delete. I tested scenarios pertaining to Bulk Edit and Import and it was bombing in cases where multiple Sprints were removed.

Specifically, the web request API call was failing because it was passing multiple ID's in the URL parameter. To address this, I simply added a For Each to loop through the ID's. I'll update the screenshot of the Automation details in my original reply.

Also, I just want to note that the {{deletedFieldChange.valueIds}} smartvalue appears to be reporting the correct values even when multiple Sprints are deleted from the Issue.  ...still keeping a close eye on it though.

Cheers!

[Dan -minware-]

Hey @abhishek_sharma03 -

If you are open to working with a 3rd party this is available "out of the box" in minware.

Here is a screenshot of our sprint trends reports - you can see rollover (teal) and multi-rollover (green):

The report can be expanded to show the individual tickets / work that is rolling over.

We offer a free trial if you want to check it out!

[Apryl Harris]

@Dan -minware- it is Minware Data Ingest app that you are referring to or some other app?

[Dan -minware-]

Yes, correct - @Apryl Harris you are looking for the minware Data Ingest App. We have made a ton of updates since August of last year and I hope you like them. Please reach out to me directly (dan at minware dot com) if you have any questions!

[Hannes Obweger - JXL for Jira]

Hi @abhishek_sharma03

to add to @Jack Brickey's answer, if you're open to a solution from the Atlassian Marketplace, this would be easy to do using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a number of so-called smart columns that aren’t natively available, including the number of sprints. 

This is how it looks in action:

As you can see above, you can easily sort and filter by the number of sprints, and also use it across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes