Use math expression with look up action

[Maurice B_]

Hey Guys, 

I'm currently struggling with math expressions resulting from a look up action. 

The rule looks like this (removed the content for privacy reasons): 

What I would like to have is a weekly report for all selected issues from the look up action. I have set up a custom numeric field "completion in %" and would like to multiply story points with the completion in % 

As far as I understand the syntax, I tried to use the following math expression: 
{{#=}}{{LookupIssues.Story Points}} * {{LookupIssues.customfield_11965}} / 100{{/}}

(note: for one single issue with manual trigger I used the expression above with "issue.Story points" instead of "LookupIssues.Story Points" and it worked, but this is not what I am looking for)

In the send e-mail action I would like to have one email with a table that contains the result from that math expression for all issues found within the look up action. 

Is there a better way to set up the rule or do you find any mistakes? 

Thank you for your support. 

[Bill Sheboy · Accepted solution]

Hi @Maurice B_ 

First, smart values are name, spacing, and case-sensitive.  When an incorrect one is used, it evaluates to null, often leading to expressions failing to work as expected.  The smart value for the lookup issues results is {{lookupIssues}}

Next, the result of a Lookup Issues action is a list of issues, with zero-to-many in the results.  And so they must either be iterated over or summarized for a field, such as with the sum or average functions.

Let's assume you want to list the value for each issue.  To iterate over the issues and perform the math operation, please try this expression:

{{#lookupIssues}}
* {{key}} - {{summary}}: {{#=}}ROUND({{Story points|0}} * {{customfield_11965|0}} / 100, 2){{/}}
{{/}}

I added a default value of 0 for each field to handle cases where the field is not set, and the ROUND() function so you can decide how many decimal places you want to show.  (I used 2 digits for the decimals.)

Finally, what type of project are you using: Company-managed or Team-managed?  These use two different "story points" fields, so confirm you are using the correct one:

• Company-managed use "Story points" with the smart value {{issue.Story points}}
• Team-managed use "Story point estimate" with the smart value {{issue.Story point estimate}}

Kind regards,
Bill

[Maurice B_]

Hi 

@Bill Sheboy

thank you so much for you answer, it perfectly worked! We are using company-managed "Story points", so your result was fine. 


The result looks like this now in my email, this is exactly what I was looking for:

Now I have one more question: Do you have a proposal how to summarize all values from column Story Points and all values from column erl. story points? 

Is it like 

{{#=}}({{lookupIssues.Story points|0}} * {{lookupIssues.customfield_11965|0}} / 100, 2).sum{{/}}

Thank you, really appreciate your support! 

[Bill Sheboy]

Awesome; I am glad to learn that helped!

Earlier I noted either iterating or summarizing the results of the lookup.  And so outside of the iterator, the summarizing could be done.  For example:

{{#lookupIssues}}
* {{key}} - {{summary}}: {{#=}}ROUND({{Story points|0}} * {{customfield_11965|0}} / 100, 2){{/}}
{{/}}

Total Story Points: {{lookupIssues.Story points.sum|0}}
Average % Completed: {{lookupIssues.customfield_11965.average|0}}
...

Please adjust to the math you need as I am not clear on your last calculated value, and summing the % Complete did not make sense so I averaged it :^)

[Maurice B_]

Hey @Bill Sheboy , 

the total sum of story points works fine. 

What I am calculating is the sum of resolved story points, as Jira is only using the status "done" or "in progress" the actual progress is not visible. So this is why I am using the formula like "story points * percentage of completion = number of resolved story points". 

And what I would like to have is a total of all resolved story points from the lookup issues. 

Any suggestions how to do that? 

[Bill Sheboy]

Let's assume your Lookup Issues action is returning all of the issues needed, regardless of their status.

With smart value, list filtering and a math expression, to "total completed story points" would be this:

{{#=}}0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+{{Story points|0}}{{/}}{{/}}{{/}}

That works by:

• iterating over the issues in the lookup results
• filtering to only use the ones with a Status Category of "Done"
• adding the Story points
• and wrapping all of that in a math expression, with a default value of 0 at the front

The same approach may be used to find the percentage of completed issues by story points (by just dividing by the total amount and rounding):

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+{{Story points|0}}{{/}}{{/}} ) / {{lookupIssues.Story points.sum|0}} * 100, 0){{/}}