How to automate the average of a numeric custom field of all child issues of an Epic

Question

Hello!

I've created a custom field 'Avance completado (%)' which contains the progress in percentage (values between 0 to 1 values) of an issue (I'm working with Epics, Tasks, Subtasks).

I'm traying to create an automation so I can average 'Avance completado (%)' of all the child issues of an Epic and put the result in the same field of the Epic (parent). Here is an example:

* Epic A* Task 1 -> 'Avance completado (%)' = 0.2
* Task 2 -> 'Avance completado (%)' = 0.5
* Task 3 -> 'Avance completado (%)' = 0.3

So,

Epic A -> 'Avance completado (%)' = (0.2 + 0.5 + 0.3)/3 = 0.33

I've been trying to achieve this goal with the following automation:

My main problem is that I don't know how to reference this custom field and average it whithin the automation. I've been trying with different approaches but none of them have worked:

* {{issue.subtasks.customfield_10187.average|0}}
* {{issue.subtasks.Avance completado (%).average}}
* {{issue.subtasks.[Avance completado (%)].average}}
* {{issue.subtasks.customfield_10187.value.average}}

Do you guys have an idea of how I can accomplish this goal?

Thanks in advance!!

Hannes Obweger - JXL for Jira · Answer

Hi @Elizabeth Basurto

welcome to the community!

I trust that you'll find an Automation based solution.

Just as food for thought: If you're open to solutions from the Atlassian Marketplace and don't want this to bleed into your Automation budget, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a rangle of advanced features, including support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

With these, you can build a view like e.g. this in just a couple of clicks:

Test: Number is a custom field.

This is really just one of a virtually endless number of possible views and reports; you can also view and group by any other issue fields, configure different sum-up styles, etc. etc.

This all just works - there's no scripting or automation whatsoever required.

Any questions just let me know,

Best,

Hannes

Bill Sheboy · Answer

Hi @Elizabeth Basurto -- Welcome to the Atlassian Community!

You rule is triggered and checks if the issue is a Task type, then branches to the parent (an Epic) and then tries to reference Subtasks of that Epic.  That will not work, as Epics do not have Subtasks.

And what do you mean by "average" in this context: the average of the values in the Tasks or the average across all subtasks?  For example:

* Epic* Task A* Subtask A.1
* Subtask A.2

* Task B (has no Subtasks)
* Task C* Subtask C.1
* Subtask C.2
* Subtask C.3

Which of these to you want for the "average", or something else:

( (Subtask A.1 + Subtask A.2) / 2 ) + ( Subtask C.1 + Subtask C.2 + Subtask C.3) / 3 ) / 2

or

(Subtask A.1 + Subtask A.2 + Subtask C.1 + Subtask C.2 + Subtask C.3) / 5

These may produce two different results, and knowing which one is needed will help you build your rule correctly.

Kind regards,
Bill

Max - SwiftSprint dev · Answer

Hi @Elizabeth Basurto ,

Welcome to Atlassian Community.

You can use the following formula

{{#=}}{{#=}}0{{#issue.subtasks}}+{{customfield_10187}}{{/}}{{/}}/{{issue.subtasks.size}}{{/}}

It iterates through all subtasks, summarise the custom field values and divides on number of subtasks.

Additional info:

* https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/ 
* https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/