nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Automation rule to calculate time forecast based on storypoints fails

Martijn Hoekstra

Hi community,

I am trying to get the following using a Jira Automation rule (company managed Jira):

By triggering an automation rule by logging an hour (using Clockwork for Jira) on story level (or spike, task, bug etc):

1. Find the associated parent epic
2. Fetch the sum of the story points of all child issues of that epic -> create a variable (named: TotalStoryPoints)
3. Fetch the sum of the story points of all child issues of that with status 'Done', create another variable (named: StoryPointsCompleted)
4. Deduct StoryPointsCompleted from TotalStoryPoints and multiply by 5 (which is our long-term average of hours we spend per storypoint) to update a custom field I created on epic level (Time Forecast)

JQL for step 2:
JQL step 2.jpg
Variable for step 2:
Variable step 2.jpg

JQL for step 3:
JQL step 3.jpg

Variable for step 3:

Variable step 3.jpg

Math for step 4:
Edit issue fields.jpg
Seen a few almost similar cases on the community forum, but don't seem to find the exact solution. Managed to fetch the right input issues, but esp. the calculation at the end doesn't seem to work. Any help is greatly appreciated! Thanks, Martijn

Accepted answers

Bill Sheboy

Hi @Martijn Hoekstra

Ignoring for now the idea of using story points rather than just time-based estimation...

For a question like this, please post an image of your complete automation rule, images of any relevant actions / conditions / branches, an image of the audit log details showing the rule execution, and explain what is not working as expected. Those will provide context for the community to offer ideas. Thanks!

Until we see those...

For your step 2, lookup issues action, image, the JQL is invalid: the smart values have the incorrect casing and the epic link is not compared to anything. And, the use of the lookup in the JQL is not how either JQL or that action work. Which issues are you trying to access with that JQL?

For your step 2, created variable: variables are always text. There is no need to convert to a number for this case as there is no math operation...just a value.

And...when using variables (in create variable or branches), I suggest not using any names which could be confused with existing smart values (e.g., Story points). Instead add a prefix so they are always unique. For example, varStoryPoints.

For your step 3, lookup issues action, image, the JQL is invalid, in the same ways as for step 2.

For your step 3, created variable: same issues as for step 2.

For your step 4:

this is the place where the created variables need to be converted with asNumber to succeed in math operations
the rule is using the inline form of math operations, and so does not need to be wrapped with {{#=}} and {{/}} Instead it could be:
- {{TotalStoryPoints.asNumber.minus(StoryPointsCompleted.asNumber).multiply(5)}}

Without seeing your entire rule, I wonder if any of those calculations are needed, or if instead you could use the single Lookup Issues action with smart value, list filtering to get the numbers. Please look here to learn about that feature: https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

Kind regards,
Bill

All comments

Bill Sheboy

Hi @Martijn Hoekstra

Ignoring for now the idea of using story points rather than just time-based estimation...

Until we see those...

For your step 2, created variable: variables are always text. There is no need to convert to a number for this case as there is no math operation...just a value.

For your step 3, lookup issues action, image, the JQL is invalid, in the same ways as for step 2.

For your step 3, created variable: same issues as for step 2.

For your step 4:

this is the place where the created variables need to be converted with asNumber to succeed in math operations
the rule is using the inline form of math operations, and so does not need to be wrapped with {{#=}} and {{/}} Instead it could be:
- {{TotalStoryPoints.asNumber.minus(StoryPointsCompleted.asNumber).multiply(5)}}

Kind regards,
Bill

Martijn Hoekstra

hi @Bill Sheboy ,

Thanks so much for your elaborate feedback. Please note it's not my goal either to go into discussions around story points vs. time bases estimation, been there, not doing that again haha

For step 2 I'd like to fetch all issues under the epic of the trigger issue and the sum of their Story Points.

For step 3 I'd like to fetch all Done issues under the epic of the trigger issue and the sum of their Story Points.

I do not seem to get this right however, tried a few adjustments based on other items in the community and help pages provided.

Thanks again for your guidance

Cheers, Martijn

Martijn Hoekstra

hi Bill and others,

In the meantime I adjusted the automation to the below:

entire automation rule.jpg

I adjusted the JQL correctly I think. I use it twice to relate the lookup to the right epic.
The first variable looks like this, I used the suggested naming convention.

Variable step 2.jpg

The next variable looks like this:
Variable step 3.jpg

And finally the math part looks like this now, as suggested by Bill. (no screenshot, as it doesn't fit my screen):

{{varTotalStoryPoints.asNumber.minus(varStoryPointsCompleted.asNumber).multiply(5)}}

It seems to work, but returns 0 in the target field. The log looks like below.
audit log.jpg

It seems it returns the same value in the 2nd part, so the result is 0. Any idea how to fix that?

Thanks so much!

Kind regards, Martijn

Bill Sheboy

You are using the Lookup Issues action with what appears to be the same JQL twice. Is that correct, or is it just not showing all of the JQL in the rule image? Each could match what you are trying to do:

all story points: "Epic Link" = {{triggerIssue.parent.key}}

completed story points: "Epic Link" = {{triggerIssue.parent.key}} AND status = "Done"

For your created variable actions, please note that smart values are name, spacing, and case sensitive...and often they do not match the field names on the Jira views. For a company-managed project, the correct one is "Story points" and for a team-managed project, it is "Story point estimate". Tip: here is a how-to article to determine the correct smart values for a field.

And so for the varTotalStoryPoints variable please try this:

And for the varStoryPointsCompleted variable it would be the same as the JQL will limit the issues to just the completed ones.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

jira-software-cloud

automation