Average calculation

[Douglas Oliveira]

Hi!

I have a field number in my tasks and subtasks and i need that this field in my task be a average of the subtasks.
How i do this?

[Bill Sheboy · Accepted solution]

Hi @Douglas Oliveira 

As you are apparently using Jira Cloud, you could use the built-in automation for Jira rules to update the task to be the average of the sub-tasks' values.

The difficulty of this depends upon the field you need averaged.  There are some fields supported by the Lookup Issues action that make that easy.  Otherwise you would need to gather the issues using the REST API and JQL from the rule to do this.

Kind regards,
Bill

[Douglas Oliveira]

Thanks @Bill Sheboy ,

In my case is the same field for the tasks and subtasks, this field type is difficulty of to find in JQL search?

By the way, do you know the fuction for calcule average? i searched in Jira suport but i don't find...

Kind regards,

Douglas 

[Bill Sheboy]

Douglas, as that is a custom field it will not be listed in Lookup Issues, but with subtasks you can do this with average.  With an automation rule that would be something like this:

• trigger: value changes for your custom field
• condition: issue type is subtask
• branch: for parent
  • action: edit your field with the average value.  Assuming your field is named Progresso that would be

{{issue.subtasks.Progresso.average|0}}

[Douglas Oliveira]

Thanks @Bill Sheboy 

i belive that this rule would will work, but i have some diferents subtasks, in this case i havent how to use this condition. I tried use a branch but doesnt work, you know some way to do this? 

[Bill Sheboy]

That smart value I provided will average for any subtask types.  If you having trouble using the smart value, then:

• select your field for edit
• start typing in the smart value in the Value, and it will appear below the field
• once you finish typing the smart value, select it below the field, and it will be used

[Douglas Oliveira]

Thanks again, @Bill Sheboy 

I tested the smart value and its worked good, but the trigger is the problem, i used in this way how on the picture.

but doesn work.

[Bill Sheboy]

What is the problem with the trigger: is it not picking up changes for your custom field?

[Douglas Oliveira]

@Bill Sheboy  sorry for the delay.

Yes, the trigger it's not picking up changes.

[Douglas Oliveira]

I get finded the trigger error, Nothing happens because the field "Progress(%)" is changed by a other rule when i chang other field. I needed of add other fields for my Trigger

Thanks for the help!

[Bogdan Gorka]

Thanks @Bill Sheboy 

I used your solution with linked issues using Smart Values

{{issue.issuelinks.outwardIssue.Progress.average|0}}

 and it worked as well!

It means that one can use also linked issues.

My automation looks something like this:

• trigger: value changes for your custom field Progress
• condition: issue type is 'Work Item'
• branch: for linked issues, link type 'is delivered for'
  • action: edit your field 
    {{issue.issuelinks.outwardIssue.Progress.average|0}}

[Bogdan Gorka]

Hello @Bill Sheboy . I hope you will be so kind and extend a bit your solution.

Question 1:

Could you please help me with a small extension of the solution we discussed above?

I know now how to calculate the average of ALL outward issues, no matter what linke type they use. Is there a way to select only ONE issue link type and calculate the average? I have now two link types in my config and the average is then false.

Question 2:

Do you know where I can learn more about what methods are possible to be selected in Smart Values.

Like in our example:

issue -> issuelinks ->outwardIssue - where can I check that?

The documentation does contain some examples but where is the whole library with what is possible to be used?

I am looking foward to your reply.

[Bill Sheboy]

Hi @Bogdan Gorka 

For your question 1, you could try a couple of things:

• This will most likely work: use a lookup issues action with JQL to find the desired linked issues (by type), and then use average as you did with the other list functions.
• This may not work: try the smart value, list filtering functions to reduce the linked issues list to gather the data, and then average.  I recall another post where someone tried this filtering on inward link fields and it did not work, but you may figure it out better  :^)

Regarding question 2, I think you are asking a couple of questions here, and I wish there was better documentation in this area:

1. What issues fields are available when starting from an issue and accessing its linked issues directly (inward and outward)? Answer: I suspect only a few fields.  If all of the fields were accessible, those could lead to links and loops of links.  Potentially the automation rule could pull in every issue in the instance...up to the 100 issue limit for an automation rule.
2. What functions are available on any fields in a list?  Answer: This one should match what is in the documentation, other than improvements Atlassian is making over time: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/

Kind regards,
Bill

[Bogdan Gorka]

Thank you very much @Bill Sheboy for the prompt reply.

I found out (too late) that the lookup issues action is available only in the Cloud and I wasted some time for looking for it. I checked also that there are no plans to roll it out (the lookup action) to DC/Server any time soon. Unfortunately the discrepancy between functionality of Automation in Jira Cloud and DC is not a good thing which, in my case, will necessitate the fall back to ScriptRunner in more advanced scenarios. 

I hoped for some time that Automation Cloud will be just two steps ahead of Automation Server/DC and the latter will catch up, only later. Unfortunately it is not happening and these two will probably differ more and more, ie. Automation Server/DC will not be further developed. One can experience it very easily when comparing the SmartValues documentation which you referenced for Cloud and the DC version here: https://confluence.atlassian.com/automation/smart-values-993924860.html

Thank you again for your post.

[Bill Sheboy]

My apologies as I did note you were using the Server/Data Center version.

Instead of Lookup Issues, the Server/Data Center version has a "bulk handling" option that can be used for a branch (or scheduled trigger with JQL).  Just select that option and then use the {{issues}} smart value.  Note that name is plural, indicating it is treating the results as a smart value list.  The same functions should work with that list.

https://confluence.atlassian.com/automation/bulk-handling-of-issues-993924653.html

[Bogdan Gorka]

Hello again @Bill Sheboy 

First of all, no need to apologize :) I wasted the time first and then asked you for help. However, it was not all wasted, because I finally found the answer to where to find SmartValue names for specific fields. There is an article explaining this here: 

https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

And I am very grateful for the hint you gave me about the "bulk handling". This works! With this option on, the result of cycling through the list of issues is treated as a list of values and indeed we can then use some of the SmartValues for lists.

Below is the screenshot of the solution for others. Together with the 'average' formula that we discussed earlier (above), this now calculates the average of values in Custom Field 'Progression' only for outward links of specific type.

[Bogdan Gorka]

[Bill Sheboy]

Well done!  I often reference that how-to article to confirm smart value names, spacing, capitalization.

Now if you want to use that average you have created to do something like edit the trigger issue, the options to use that value are a bit limited:

Once inside of the branch you can no longer use Edit Issue to change the trigger issue, as it is not the "current" one; the branch issues are.

A work-around would be to call the REST API at that point for an edit on the trigger issue.  Another would be to add a "magic comment" to one issue from the branch, thus triggering a second rule which could update the original issue via a branch.  Both seem a bit convoluted to solve an edit.

[Bogdan Gorka]

You are right. Once inside of the branch all you can do is to write the average in every linked issue - you cannot go out of the loop. 

But, I will give it a go with the REST API. I found out during my intensive exercises that there is an easy way to use SmartValues and get a unique link address of an issue, which is then displayed in JSON format. 

Use the Action: "Log Action" and paste this. This will give you this uniqe link.

{{issue.self}}

[Bill Sheboy]

Your first paragraph is interesting to me: I thought when the "bulk handling" option is selected for Server/Data Center version the branch executes once and not for each issue.  If for each issue, calling the REST API n-times may not be what you want!

I recommend confirming this with an experiment inside that branch with 3 test runs:

1. write to the audit log first with a value that does not reference any issue fields (e.g. "Hello, world!") and hopefully that writes one-and-only-one time
2. then try writing with {{issue.key}} which I hope fails as there is no single "current" issue any longer
3. then write with {{issues.size} to hopefully also see one-and-only-one value

Sorry, I do not have that Jira version so I cannot test this.

[Bogdan Gorka]

I did some the testing you mentioned earlier and, to be honest, I was disppointed with the fact that log action is written only once but when you put the same value in the Edit action, then it is written in every issue in the loop. I am happy to share the results of my experiments.

Below is the Automation Log result items from the enclosed picture

1. this is the average formula, correctly calculated, taking into account only the issues linked with the link type: "is Objective of" - this is what we discussed earlier that it would be great to get that output outside the loop and put it into the triggering issue.

2. this is the test showing that the log will write only one value, if you use SmartValue "issue" and not "issues". This will take the last issue in the loop into account. If you use issues, the result will be 3

3. this is the test showing that the log writes only the data from the last issue in the cycle, if you use SmartValue "issue" and not "issues".

4. as soon as I put the same formula from point (3) and edit the Description value, so it is action: Edit, field: Description, value: {{issue.key}}, then the automation does that operation three times, because I have three linked issues.

So, I suspect, if I did it with action "Send web request" to use REST API, these calls would happen 3 times as well, unless there is some trick (with the condition?), which will stop the loop after the first linked issue update.
If it happened only once, then it would make sense to use it as trigger for another Automation. Then I could find the "parent" of the triggering issue and copy the value. Not so an elegant solution but it would be good to have it available.

Anyway, this is a very time engaging exercise but expands the horizons and the know-how about Automation.

[Bogdan Gorka]

Good news!

The REST API call inside the loop "For Linked Issues" was fired only once and I managed finally to update the custom field 'Progression' with the average calculation.

That was a really long journey.

Thank you @Bill Sheboy for all the good ideas and tips you have given.

[Bill Sheboy]

Awesome!  I am glad that worked...particularly because as automation is improved I can imagine some alignment of features between Cloud and Server/Datacenter:

• Server / Datacenter gets Lookup Issues and Created Variables...
• Cloud gets the "bulk handling" added to Branch and Scheduled Trigger...

Have a great day!

[Douglas Oliveira]

Hello everyone,

I had this question a while ago, and now I'm having a similar problem. I need an Epic field to average for the same field as Epic tasks.
I tried using:

{{issue.storys.Progresso(%).average|0}} or {{issue.tasks.Progresso(%).average|0}} but it didn't work, does anyone know how to do this automation?

[Bill Sheboy]

Hi @Douglas Oliveira 

Are you using the Jira Cloud or Server/Data Center version of automation?

For Cloud: use a Lookup Issues action with JQL to find the child issues of the epic, and then use the list function average on the results

For Sever/Data Center: use a branch with the bulk-handling enabled...with the same JQL to get the results.

Kind regards,
Bill

[Douglas Oliveira]

Hi @Bill Sheboy 

I'm using Jira Cloud. What smart value can I use to find the child issue of the epic, so far I can only search the epic from a child and not the other way around.

[Bill Sheboy]

The JQL you need depends upon the triggering issue...

If the epic is triggering the rule, its key is what you need.  To find the children try this JQL:

"Epic Link" = {{triggerIssue.key}}

If instead a child of the epic is triggering the rule, you use that child's epic link to find the others, and then branch to the epic for the update.  The JQL to find the sibling children is:

"Epic Link" = {{triggerIssue.Epic Link}}

The above are for a company-managed project (which uses Epic Link to conned epic/children).  If this is for a team-managed project substitute parent for Epic Link.

[Douglas Oliveira]

Thanks @Bill Sheboy , using this way I've made some progress but I still can't get the average of the values.
This is my situation at the moment

[Bill Sheboy]

To make the edit, you now want to use the lookup issues result as your source for the issues:

{{lookupIssues.Progresso(%).average|0}}

If that does not work due the the name of the custom field confusing the rule, try to use the custom field id name instead.  You may find the name using this how-to article: 

https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

[Douglas Oliveira]

Thank you @Bill Sheboy  it really works and the article is very good, but for some reason I am not able to open the URL of the issue to better understand how to get the smartvalues I will check if it is a restriction of the company's connection. By the way if the trigger of the rule was a subtask within a task that is inside Epic, how could I activate the rule? I was thinking about this because subtasks don't have the Epic Link.

[Bill Sheboy]

For a subtask triggered issue, you would need to walk up two levels: first identify the parent of the subtask, get that issue to then find the epic link.

[Douglas Oliveira]

@Bill Sheboy  I tried doing it this way but the calculation didn't work for epic.
I tried to make everything one rule.

I used for parent: {{issue.subtasks.Progresso(%).average|0}}

And for Epic: "Epic Link" = {{Issue.parent.Epic Link}}

Like the picture.

[Bill Sheboy]

It appears you are performing that lookup issues inside of the branch on the parent Epic, and so when it references {{issue.parent.Epic Link}} I do not understand what that could be referencing...and apparently neither does the rule.  

That would be something like: for the Epic > parent > Epic.  Which is probably not what you wanted.

To help clarify what you need in a rule, would you please answer:

• At what level are you manually entering a value for Progress %: subtask, story/task, or epic?
• At what levels should the Progress % be automatically calculated?
• For each automatic calculation, what should the value be?  For example, should it be minimum, maximum, average, or something else?

Knowing that will help make a rule to meet those needs.  Thanks!

[Douglas Oliveira]

Really this rule doesn't get you anywhere, I modified it earlier.
Well I can do both calculations well but the problem is still the trigger is the subtask since there is no epic link.
I am currently using the following structure:

In lookupissue: "Epic Link" : {{issue.parent.Epic Link}}
And for Branch JQL: Issuetype = Epic AND "Epic Name" = "{{issue.parent.Epic Link}}"

but isnt work.

[Bill Sheboy]

Your JQL in the branch is trying to match on Epic Name and you want to use key in order to get the epic:

issueType = Epic AND key = {{issue.parent.Epic Link}}

[Douglas Oliveira]

Its work Thank you , its the first time that i work with the Epics but i understand more now.