Automation to Calculate % Complete for Child Issues Using Lookup Issues at Initiative Level

Rafaella Sena October 29, 2024

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:

  1. Custom Field: I created a custom field, Initiative Progress %, to display the completion percentage at the Initiative level.

  2. Automation Rule:

    • Trigger: Issue Updated
    • First Lookup Issues: This fetches all Epics under the Initiative:
      "Parent Link" = {{triggerIssue.key}} AND issueType = Epic
    • Second Lookup Issues: This fetches all tasks under the Epics from the first lookup:
      "Epic Link" in ({{#lookupIssues}}{{key}}{{^last}},{{/}}{{/}})
  3. 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}}{{/}}

Screenshot 2024-10-29 at 3.43.42 PM.png

The Issue:

 

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.

 

Screenshot 2024-10-29 at 3.44.09 PM.png

 

It seems like the calculation isn’t picking up the completed tasks correctly, even though I expect it to filter for "Done" statuses.

 

Screenshot 2024-10-29 at 3.44.52 PM.png

Any insights on what might be going wrong or further troubleshooting steps? I’d appreciate any help! Thanks so much!

3 answers

2 accepted

0 votes
Answer accepted
Trudy Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 29, 2024

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/

Trudy Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
October 29, 2024

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.

Like # people like this
0 votes
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 29, 2024

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

0 votes
Rafaella Sena November 18, 2024

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! 

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 18, 2024

Hi @Rafaella Sena 

Based on that error message / audit log entry, the Lookup Issues returned no issues.  Is that what you expected?

Kind regards,
Bill

Smita
Contributor
December 3, 2024

I am looking for similar solution and I have the same error too. In my case the custom issue type has issues linked to it.

Trudy Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
December 3, 2024

@Smita 

Please post screen images showing your entire rule and the details of each step, and the output in the rule Audit Log.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events