How to use MAX and IF functions with smart value calculations in JIRA Automation

[Attila Madarasz]

I'm attempting to calculate a RICE score using Automation for JIRA.

I've defined custom fields for Reach, Impact, Confidence, Effort and Score and have those associated with any issue.  Each of those fields are in effect choice fields.  The confidence field has choices of None, Unknown, High, Medium and Low .. so I can't use the fields value directly .. I need to be able to map them to numeric fields .. which I can then use to calculate a Score eventually.

Obviously what I tried first didn't work, since I tried to use a combo of MAX, IF and {{smart }} values .. and kept on getting errors related to the IF statement getting 4 values when it only supports 3.

So I simplified it to just try a single statement like ..

{{#=}}IF(({{issue.[RICE] Reach.value}}=Everyone),10,1){{/}}

inside a "Then: Edit issue fields" action triggered manually (for testing purposes)

Which then gives me another strange error ..

"Error rendering smart-values when executing this rule:Unknown operator or function: Everyone: IF((Everyone=Everyone),10,1)"

Ultimately, what I'm trying to achieve is to put the following calculation into the "Then: Edit issue fields" action ..

{{#=}}
(MAX(
IF(({{issue.[RICE] Reach.value}}=Everyone),10,1),
IF(({{issue.[RICE] Reach.value}}=Significant),7,1),
IF(({{issue.[RICE] Reach.value}}=Considerable),5,1),
IF(({{issue.[RICE] Reach.value}}=Moderate),10,3),1
) *
MAX(
IF(({{issue.[RICE] Impact.value}}=Massive),10,1),
IF(({{issue.[RICE] Impact.value}}=High),7,1),
IF(({{issue.[RICE] Impact.value}}=Medium),5,1),
IF(({{issue.[RICE] Impact.value}}=Low),10,3),1
) *
MAX(
IF(({{issue.[RICE] Confidence.value}}=High),7,1),
IF(({{issue.[RICE] Confidence.value}}=Medium),5,1),1
)) /
MAX(
IF(({{issue.[RICE] Effort.value}}=3XL),52,1),
IF(({{issue.[RICE] Effort.value}}=2XL),26,1),
IF(({{issue.[RICE] Effort.value}}=XL),13,1),
IF(({{issue.[RICE] Effort.value}}=L),4,1),
IF(({{issue.[RICE] Effort.value}}=M),2,1),1
) {{/}}

But as I said .. it doesn't work .. but it's probably because of how I'm trying to use the IF statement I guess ?.

So, my real question is whether I'm trying something doomed to fail because it's a tad too ambitious .. or am I just not getting the syntax right .. and the above should be able to be gotten to work ?

Thoughts anyone ?.  Would really appreciate any insights :)

[Attila Madarasz · Accepted solution]

Issue solved finally.  Problem for the last attempt seems to have been a hidden character in the copy paste for the startsWith() comparison.

The following now works, though I abbreviated the string match to just the first character in most (but not all) cases ..

{{#=}}
(MAX(
IF({{issue.[RICE] Reach.value.startsWith("E")}},10,1),
IF({{issue.[RICE] Reach.value.startsWith("S")}},7,1),
IF({{issue.[RICE] Reach.value.startsWith("C")}},5,1),
IF({{issue.[RICE] Reach.value.startsWith("M")}},3,1),1
) *
MAX(
IF({{issue.[RICE] Impact.value.startsWith("Ma")}},10,1),
IF({{issue.[RICE] Impact.value.startsWith("Hi")}},7,1),
IF({{issue.[RICE] Impact.value.startsWith("Me")}},5,1),
IF({{issue.[RICE] Impact.value.startsWith("Lo")}},3,1),1
) *
MAX(
IF({{issue.[RICE] Confidence.value.startsWith("H")}},5,1),
IF({{issue.[RICE] Confidence.value.startsWith("M")}},3,1),1
)) /
MAX(
IF({{issue.[RICE] Effort.value.startsWith("3")}},52,1),
IF({{issue.[RICE] Effort.value.startsWith("2")}},26,1),
IF({{issue.[RICE] Effort.value.startsWith("X")}},13,1),
IF({{issue.[RICE] Effort.value.startsWith("L")}},4,1),
IF({{issue.[RICE] Effort.value.startsWith("M")}},2,1),1
) {{/}}

So with the above I'm now able to calculate a RICE score on demand for any ticket.  I'll keep it as a manual calc for now though.

[John Funk]

Great! Glad you were able to figure it out!

[Benoit]

Hey Found it... do not use MAX function. use nested if:

{{#=}}IF({{issue.CustomFiledName.value.startsWith("3")}},3,IF({{issue.CustomFiledName.value.startsWith("2")}},2,IF({{issue.CustomFiledName.value.startsWith("1")}},1,0))){{/}}

adapt it with your values and it should work.

[Attila Madarasz]

Yep, that should work too :).  The issue for me though was more about the the matching logic to the value.  The key was when I switched to startsWith().  Thanks in any case .. I'll use nested IF's in the future .. feels more elegant.

[John Funk]

Hi @Attila Madarasz ,

I am a little confused as to exactly what you are trying to accomplish. 

Can you walk through the exact use case?

[Attila Madarasz]

OK, so I am trying to populate a computed value into a custom field using a computation involving 4 other custom fields.  The source custom fields are single choice fields I’ve setup with values like high, medium, low, etc.  But to use those fields for a computation I need to assign a numeric values to each option and then use that numeric value for the computation.

Ultimately, the ‘score’ I want is calculated as 

score = (confidence x reach x impact) / effort

So, I wanted to convert the selected value for each field into a number, then do the calculation.

Reading the documentation on smart values it would seem I should be able to achieve that as a single calculation .. something like what I’ve listed above, which is simply trying to get the MAX value for each custom field by using an IF statement that compares the fields current value to a string, and if it matches to return the value of that selection, if not to return the value of 1.  The MAX function then simply gives me the numeric value of the matched field value, since all the other options are all 1’s.  Thus, each MAX function should retrieve the underlying custom fields numeric value which I can then use in the calculation of the score I’m after.  So, for the effort field, if the value chosen is 3XL, then the IF would return 52 and all the other IFs for that MAX function would return 1, since only one IF can be true for each field.

Its not elegant, sure, but it would work methinks if the syntax is right and the smart value feature is capable.

How the calculation is triggered is a separate issue.  For now I’m just trying to figure out what’s wrong with even the stripped back calc, with the single IF statement ?.  I thought once I get the iF syntax right, I’d be able to figure out how to make the bigger calc work.

Obviously, if there’s an easier way to do this ... I’m all ears and happy to take any direction that solves the requirement without having to branch out to code if I can avoid it.

[Attila Madarasz]

Actually, I guess the problem with the IF is I’m getting the comparison syntax wrong.  Problem is I can’t figure out what the syntax is for the ‘condition’ part of the IF statement.  All I’m trying is to compare the fields value to a string.  The IF would then return either the value for condition being true or false.

[Attila Madarasz]

Checking the docs, just realised I’m trying to compare strings with numeric equality when obviously I should be using string equality syntax.   Rookie mistake.  It kinda also helped to stumble across a link from the docs out to Apache.org StrinGUtils docs from the Atlassian docs .. explains a lot.

[John Funk]

Sorry - just now getting back to this. Were you able to implement a solution?

[Attila Madarasz]

Nope, haven't gotten past this yet.

I've simplified the smart value to just the following ..

{{#=}}IF({{issue.customfield_10035.value.startsWith(“Every”)}},10,1){{/}}

since I'm now just trying to solve the string to match.  Looking at the API response for that field ..

"customfield_10035": {
"self": "https://teachershealthit.atlassian.net/rest/api/2/customFieldOption/10004",
"value": "Everyone",
"id": "10004"
},

The actual value of the field is "Everyone" .. so I can't see why ..

{{issue.customfield_10035.value.startsWith(“Every”)}}

Isn't returning true ?

[Benoit]

Did you manage to make it works? I have similar problem. It looks like numeric and text are not well managed when mixed together.