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:

Variable for step 2:

JQL for step 3:

Variable for step 3:

Math for step 4:
 
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

[Bill Sheboy · Accepted solution]

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

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

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.

The next variable looks like this:

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. 

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:

{{lookupIssues.Story points.sum|0}}

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

[Martijn Hoekstra]

Hi Bill,

Good morning. The automation rule now does exactly what I want, a big thank you is in order! The variable actions indeed did not work due to incorrect spacing StoryPoints instead of Story points.

Again thank you for your help and have a great day

Kind regards,
Martijn

[Bill Sheboy]

Awesome; I am glad to learn that helped!