calculated fields based on Sprint end-date

[kate reeder]

Hi folks - we are using JIRA Software on a Scrum board, company-managed project. I'd like to add a couple fields to the issues that will auto-calculate a date based on the end-date for the aligned sprint. I don't want users to be able to change these values manually (so I admit maybe 'field' is the wrong wording here). I will be using this in some dashboard reporting (will be running queries based on the values in these fields). When I try to create a custom field for this, I don't see an option for a calculated date field. ...maybe Custom Fields isn't the right way to go about this. Also want to note that we are using Rich Filters (plugin) for our dashboard reporting. Does anyone know how I can achieve the result I'm looking for? 

Due Date Field: auto-calculate based on the end-date of the aligned sprint

Dev Cut-Off Field: auto-calculate based on the end-date of the aligned sprint, minus 15 calendar days. 

If we are able to create calculated metadata for issues, I will then want to make sure this data automatically updates if/when the issue is moved to a new sprint (with a new end-date). 

Here are a couple use cases as examples for how we'll use this metadata

USE CASE #1: On the dashboard, display a warning (Smart Filters) when testing is incomplete for tickets that are close to the end of the sprint. JQL: TestingComplete = No AND today's date > DevCutOff OR TestingComplete = No AND status IN ("Complete-On Hold", "Complete-Ready for Code Push", "Complete-in UAT", "Complete-In Production")

USE CASE #2: On the dashboard, display a warning (Smart Filters) when development is incomplete on a ticket and we're getting close to the end of the sprint. JQL: status NOT IN ("Complete-On Hold", "Complete-Ready for Code Push", "Complete-in UAT", "Complete-In Production") AND DevCutOff < today's date

[Bill Sheboy · Accepted solution]

Hi @kate reeder 

You might be able to set those fields using an Automation for Jira rule, triggered on Sprint Start, assuming you only have one active sprint at a time you rarely add scope to a sprint.

The display on the dashboard may be done with saved filters on filter list gadgets.

Your "Dev Cut-off Field" could be problematic for an agile team using scrum, as that seems to imply a fixed amount of dev/validation/releasing for every story, regardless of sizing.  Please consider that before trying to build this.  Thanks!

Kind regards,
Bill

[kate reeder]

Thanks for the input, @Bill Sheboy Dev Cut-Off may not be the right name for this field... the intention of the field (outside of the noted use cases) is to signify a date in the sprint after which we won't allow any additional issues to be added to the sprint. Maybe New Issue Cut-Off is a better name for this field. The team is new to agile and not yet used to giving proper sizing, so they're in the habit of adding things to the sprint after the sprint start date bc they have avail bandwidth to absorb additional items. However, after a certain point in the sprint we don't want them doing this bc the testing team wouldn't have time to complete proper testing - for example, if things are added to the sprint a day before they're scheduled to push code. We're working through a culture-shift with the team moving to Agile from Waterfall and reporting like this will help us gather metrics to illustrate the impact of bad habits.

Having said this, we have overlapping sprints so technically we have two active sprints at a time. We do monthly sprints and start the next sprint 2 weeks before the end of the last/current sprint. During that 2 week time period, the first sprint is going through a UAT code release and then a week later a Prod code release. Starting the next sprint when the current one moves to UAT allows the dev team to start coding efforts for the new sprint since they can no longer do any coding for the current sprint. ...hope this is making sense. Given that we use overlapping sprints, I'm guessing your suggestion for the automation rule won't work for us (based on your comment that we have only one active sprint) ...thoughts? 

[Bill Sheboy]

As you have described that need, I do not believe there is anything built-it which could prevent adding items to the sprint after that threshold.

However...would a reminder email/notification help when someone does this?

If so, you could use an automation rule to detect when someone adds something to an in-progress sprint, compare that to the sprint end date, and send the reminder.  (The rule could automagically remove the item from the sprint, but discussion/education may be better than just removing the item.)

[kate reeder]

Thanks for the suggestion, @Bill Sheboy I don't actually want to prevent them from adding items to the sprint when the sprint is already in progress, I just want to be able to report on it when they do add things mid-sprint. I suppose I could use the automation rule to send myself emails when they add things mid-sprint and then I could manually track those. It would be better if I could just report on those scenarios through a dashboard or something though. 

Back to the initial use cases cited above... the main intention there is to report on and track items that are still open when we're close to the end of the sprint. I was going to use the metadata described above (Cut-Off and Due Date) to add smart filters to my dashboard, but I need the metadata to pull from in order to do this. Going back to your initial suggestion, I want to clarify bc I haven't played with automation rules too much... Are you suggesting that automation rules can be used to populate data in custom fields? Let's use Due Date field as the example.. since JIRA doesn't have calculated fields out of the box, can I use an automation rule to look at the sprint the issue is aligned with, reference the sprint end date, and then put that date into the Due Date field? ...would this work?

[Bill Sheboy]

Yes, that sounds correct for many of your ideas. 

And also, you note the team is new to agile ideas.  I recommend raising these issues as they occur, at sprint planning, the daily stand-up, and at the retrospective.  Making them visible to all may reduce the needs for the reporting.

For more details on your questions/ideas...

If you want to report on sprint scope change, rather than notify people, you have a couple of options: review the built-in reports as the sprint progresses, or add an automation rule, triggered when an issue is added to the openSprints() and mark it with a label, such as Added-To-Sprint.  That could be reported upon in a dashboard.  That rule would be something like this:

• trigger: field value changed, for Sprint, where a value was added, when the issue was created or edited
• JQL condition: to check if the sprint is in the openSprints()
• action: edit issue to add your label

Next, automation rules can set built-in and custom fields.  So when the sprint starts, you could set date fields based on the data in the sprint fields (e.g. the sprint end date/time).

To learn more about automation like this, here are a few references:

• automation basics: https://www.atlassian.com/software/jira/guides/expand-jira/automation
• smart values for issues: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/
• date calculations with automation: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

And if you have problems writing your rules, please post an image of your rule back here and the community can help

[kate reeder]

This is really helpful, @Bill Sheboy !!! thank you so much for the details and extra references. I really appreciate it! :)