Hi everyone,
I'm working on an automation rule in Jira to calculate the completion percentage of child issues at the Initiative level. My hierarchy is Initiative > Epic > Task, and I want this rule to pull in all linked Epics under the Initiative and then look up the tasks under each Epic to calculate a completion percentage.
My goal is to update a custom field, Initiative Progress %, on the Initiative based on the completion of all its child tasks.
Here’s how I have it set up:
Custom Field: I created a custom field, Initiative Progress %, to display the completion percentage at the Initiative level.
Automation Rule:
"Parent Link" = {{triggerIssue.key}} AND issueType = Epic
"Epic Link" in ({{#lookupIssues}}{{key}}{{^last}},{{/}}{{/}})
Calculation Formula: I’m using this formula in an Edit Issue action to update the "Initiative Progress %" field:
{{#=}}100 * {{lookupIssues.filter("status.category.name == 'Done'").size}} / {{lookupIssues.size}}{{/}}
The formula is returning 0%, even though the Lookup Issues actions are pulling the correct set of Epics and tasks based on the audit log.
It seems like the calculation isn’t picking up the completed tasks correctly, even though I expect it to filter for "Done" statuses.
Any insights on what might be going wrong or further troubleshooting steps? I’d appreciate any help! Thanks so much!
Hello @Rafaella Sena
Welcome to the Atlassian community.
First I recommend that you print out the smart values from your Calculation into the rule execution log using the Log action to ensure the values contain what you think they should.
{{lookupIssues.filter("status.category.name == 'Done'").size}}
{{lookupIssues.size}}
I have not actually heard of a filter() function on a lookupIssues results set before. Can you share the reference you found that documents that function? The documentation for smart values that can be used with lists does not include filter().
https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/
Additionally, you don't need to use two Lookups to get the tasks under epics under an initiative. You can use the portfolioChildIssuesOf() function.
issue in portfolioChildIssuesOf("{{triggerIssue.key}}") and issuetype in (Task,Story,Bug)
Update the list of possible issue types to include all that might be children of Epics.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Rafaella Sena -- Welcome to the Atlassian Community!
First thing, where are you learning about this syntax for filtering a list? That is not a valid syntax for filtering a smart value, list, and this is the second post today I have seen with that syntax.
--This does not work!
{{lookupIssues.filter("status.category.name == 'Done'").size}}
Instead you could use this syntax to calculate the percentage complete by iterating to count the done the issues in the epics using a math expression, rounded to a whole percentage:
{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}
I also recommend changing your use of "Epic Link" to "parent", as that epic field is sunsetting over time.
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thank you so much for your help—it’s working now!
@Trudy Claspill , the portfolioChildIssuesOf()
function is elegant and was a huge help.
I used the following JQL:
{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}
@Bill Sheboy , it's functioning well, but I'm seeing this error
Division undefined: ROUND( ( 0 ) / 0 * 100, 0)
Any thoughts on how to resolve this?
Thanks again!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Based on that error message / audit log entry, the Lookup Issues returned no issues. Is that what you expected?
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please post screen images showing your entire rule and the details of each step, and the output in the rule Audit Log.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.