Automation: Calc average for customfields (check if not empty)

Beat Gurtner November 17, 2022

Hi,

 

i have 8 customfields (numeric) and I want to store an average in a other customfield:

 

This is my json code, which works almost. First I sum all the fields and then I need to divide that with the sum of all fields that are not empty. But thats the part i dont know how to do...

 

{

    "fields": {

        "customfield_11100": "{{#=}}

({{issue.customfield_11508|0}} +

{{issue.customfield_11509|0}} +

{{issue.customfield_11511|0}} +

{{issue.customfield_11507|0}} +

{{issue.customfield_11510|0}} +

{{issue.customfield_11505|0}} +

{{issue.customfield_11506|0}} +

{{issue.customfield_11504|0}})

 

/

 

({{issue.customfield_11508.length()|0}} +

{{issue.customfield_11509.length()|0}} +

{{issue.customfield_11511.length()|0}} +

{{issue.customfield_11507.length()|0}} +

{{issue.customfield_11510.length()|0}} +

{{issue.customfield_11505.length()|0}} +

{{issue.customfield_11506.length()|0}} +

{{issue.customfield_11504.size|0}})

 

{{/}}"

    }

}

 

 

 

3 answers

1 accepted

0 votes
Answer accepted
Beat Gurtner November 21, 2022

sorry this is the solution:

 

{

    "fields": {

        "customfield_11100":

                                  "{{#=}}

                                                  ({{issue.customfield_11508|0}} +

                                                  {{issue.customfield_11509|0}} +

                                                  {{issue.customfield_11511|0}} +

                                                  {{issue.customfield_11507|0}} +

                                                  {{issue.customfield_11510|0}} +

                                                  {{issue.customfield_11505|0}} +

                                                  {{issue.customfield_11506|0}} +

                                                  {{issue.customfield_11504|0}})

                                                  /

                                                  ({{#=}}

                                                                               (

                                                                               {{if(issue.customfield_11508,1,0)}} +

                                                                               {{if(issue.customfield_11509,1,0)}} +

                                                                               {{if(issue.customfield_11511,1,0)}} +

                                                                               {{if(issue.customfield_11507,1,0)}} +

                                                                               {{if(issue.customfield_11510,1,0)}} +

                                                                               {{if(issue.customfield_11505,1,0)}} +

                                                                               {{if(issue.customfield_11506,1,0)}} +

                                                                               {{if(issue.customfield_11504,1,0)}}

                                                                               )

                                                                              

                                                  {{/}})

                                  {{/}}"

    }

}

0 votes
Robert Condon
Contributor
August 23, 2023

@Stefan Salzl I like your solution here, but am having some trouble

I am always returning a 0 value on my 'fieldCounts' variable. Any ideas?

Stefan Salzl
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 23, 2023

Hi @Robert Condon 

Please add more details to your question (eg. screenshots of you automation rule as well as from the audit log).

This helps the community to get an overview of your current configuration and understand what you are trying to solve and how.

Best
Stefan

Robert Condon
Contributor
August 29, 2023

Sorry @Stefan Salzl 

The {{sum}} variable works.

The {{count}} variable always returns 0.

Screenshot 2023-08-29 100422.pngScreenshot 2023-08-29 100537.png

  • I have 2 test records.
  • Both with 2 custom fields
  • Test record 1 has a value for both custom fields
  • Test record 2 has a value for only one custom field
  • I need test record 1 to return 2 and test record 2 to return 1 in {{count}} so I can then do the calculation

 

thanks

Stefan Salzl
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 29, 2023

Hi @Robert Condon 

Could it be that your custom fields are not of type number? It´s my guess as I can see that your sum doesn´t work either. It doesn´t result a sum but concatenates to strings (0.5, 1.5).

I would recomment to check your custom fields. Furthermore you could add another log action to log the value of your custom fields.

Best
Stefan

Robert Condon
Contributor
August 30, 2023

thanks @Stefan Salzl

This doesn't seem to be the issue. I have tried with numbers and they still always resolve as 0 whether they are NULL or not

I think the reason they looked like strings, is because I am testing with 2 records. 1 has values for both fields. The other only has values for one field. That is the result of the 2 different sums show, but they both resolve as 0 for the {{count}} variable.

 

For some reason the IF statement is just not picking up the value in the custom fields

Robert Condon
Contributor
August 30, 2023

OK.

I think I have got to the bottom of this:

This seems to be because the IF function is rounding any number below 1 (0.5 for example) to zero. 

In my requirement the numbers are all between 0-1, therefore they were all returning 0.

 

I can confirm it works for all numbers >=1

 

thanks for your help @Stefan Salzl 

Like Stefan Salzl likes this
0 votes
Stefan Salzl
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 20, 2022

Hi @Beat Gurtner 

I could manage to calculate average with regard of amount of fields that are filled with setting variables. My automation rule looks as follows:

First I set a variable with the sum of all field´s values (that´s silmiar to your first part):
image.png

Then I create a variable for the amount of fields that are not empty. That was more the tricky part. I managed this with an if for each field: If true ( --> not null) --> add 1, else: add 0 (see screenshot):

image.png

 

The last part is just dividing the two variables (--> make sure to format them .asNumber as variables are stored as string by default):

image.png

 

Hope this was helpful and solves your problem.

Best
Stefan

Beat Gurtner November 20, 2022

see answer below

Stefan Salzl
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 21, 2022

Hi @Beat Gurtner 

was that helpful? can‘t find any answer below.

Best
Stefan

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events