Custom formula help

[Audrey Tolman]

Hello,

I've created a new field (not custom) that has a drop down list of questions.  Each answer has a weighted value.  I created a new field (not custom) to try to do a calculation of these answers by creating a custom formula.  I'm trying to grab the weighted value of each question, except for 2 of them.  I want to grab the weighted value that's the higher of the 2. 

I've tried all types of formats that I could find online but I keep getting invalid expression.  Would appreciate any help!

[Audrey Tolman · Accepted solution]

Here's a screenshot of the set up

[Bill Sheboy · Accepted solution]

Hi @Audrey Tolman -- Welcome to the Atlassian Community!

If you have added a field to Jira, it is a custom field.  What leads you to believe it is not a custom field?

Next, where are you trying to perform these field calculations:

• in an automation rule
• in a Jira Product Discovery project, field
• with a marketplace addon for calculated fields
• in a workflow
• somewhere else?

And would you please post a screen image showing where you are adding the calculation, including the error you observe?

Knowing this information will help the community to offer suggestions.

Kind regards,
Bill

[Audrey Tolman]

Hi @Bill Sheboy 

We've just spun up the dev environment for Product Discovery.  A custom field was not created as with the other instances.

So, here's what I've done.  I've created a field with weighted values entitled Business Assessment Questions.  There are 6 specific questions, each with answers to select from.  Each answer has a weighed value.  Here's an example.  The blue represents the name of the question (not sure how to remove the weight from those).  For each new Idea this assessment is made.  The user selects 1 answer for each question with the weighted value.  That is working fine.  Now I've created another new field called BizJF Value - as there is some math required to get the Biz Prioritization score.  I selected custom formula - here's the english version of the calculation I'm trying to come up with (I am not a developer).

current weighted value of business value + current value of balanced scorecard value + the maximum score of risk mitigation or opportunity enablement (only use the highest value of the 2 categories) + current value of time criticality = the Biz Prioritization score to be populated in the BizJF Value field.  I've been reviewing the examples under 'how to write a custom formula' but I'm obviously out of my league.

The error I get is Invalid Expression

[Bill Sheboy]

Thanks for that information, Audrey.

Would you please post an image of your calculation and the error shown?  That will help provide more context.

Until we see that...

It seems your calculation is a variation of SAFe's interpretation of WSJF (weighted-shortest, job first).  And, part of your calculation is using things like "the maximum score of risk mitigation or opportunity enablement (only use the highest value of the 2 categories)".

I do not believe that Jira Product Discovery's custom fields can perform a max() operation.  Instead you may need to use an automation for Jira rule to perform this calculation, triggered on a user changing any of the source fields.

After you show your custom field formula we can confirm that.  Thanks!

[Audrey Tolman]

Bill,

You're exactly right.  We also have another calculation for WSJF where we take the BizJF value and divide by the T-shirt size weighted value.

Here are the calculations we currently use in ServiceNow.

BizJF

if  (Number(current.u_business_readiness.getValue()) !=0) {

current.u_bizjf  =  ((Number(current.u_business_value.getValue()) + Number(current.u_balanced_scorecard_value.getValue()) + Math.max(current.u_risk_mitigation.getValue(), current.u_opportunity_enablement.getValue()) + Number(current.u_time_criticality.getValue())) / }

else {

current.u_bizjf = 0;

}

WSJF

              Var val = (Number(current.u_business<value.getValue()) + Number(current.u_balanced_scorecard_value.getValue())) =

Math.max(current.u_risk_mitigation.getValue(), current.u_opportunity_enablement.getValue()) + Number(current.u_time_criticality.getValue())) /

(Number(current.u_business_readiness.getValue()) + Number(current.size.getValue()));

              Current_wsjf = wsjf = val;

 This is the latest format I tried in Jira

{{issue.Business Assessment Questions.business value.getValue + Balanced scorecard value.get + Value = {{max.risk mitigation value.getvalue or opportunity enablement.getvalue}}/business readiness.getValue}}

[Audrey Tolman]

Here's a screenshot of the set up

[Bill Sheboy]

At this time, the custom fields in JPD are pretty limited in math operations.  You could do what you ask with an automation for Jira rule.  Your Jira Admin can help you create that in the JPD project, and here is a basic outline for your rule:

• trigger: field value changed for Business Value, or Balanced Scorecard Value, or Risk Mitigation Value, or Opportunity Enablement, or Business Readiness, or Current WSJF (please update the names/fields accordingly)
• action: Edit issue
  • select the Current WSJF field, and for the value enter this:

{{#=}} ( {{Business Value}} + {{Balanced Scorecard Value}} + max( {{Risk Mitigation Value}}, {{Opportunity Enablement}} ) ) / {{Business Readiness}} {{/}}

Please consider what you want to have happen the denominator (i.e., Business Readiness) is zero.

And after you add the rule, you can change the custom field to be a generic numeric one...so the calculations do not collide.  :^)

To get you started on creating your rule, please refer to these documentation and example sources: 

• https://www.atlassian.com/software/jira/guides/automation/overview#what-is-automation
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/
• https://www.atlassian.com/software/jira/automation-template-library#/rule-list?systemLabelId=all&page=1&pageSize=20&sortKey=name&sortOrder=ASC
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/

[Audrey Tolman]

Thanks Bill!  Last question regarding my headers I have highlighted in blue - anyway to avoid adding a weight to those?  They aren't hurting anything but it might be confusing for some.

[Bill Sheboy]

As those represent the field names, aren't they shown already on the page and then allowing the person to select a value from a drop-down?

If so, I recommend removing them.  When JPD eventually improves custom formulas you may be able to turn off the rule and having the extra entry as a "title" might break the way weights are computed.

If I am misunderstanding, would you please show a screen image of the view people see when they use that field to create an idea?

[Audrey Tolman]

No, the field name is Business Assessment Questions.  The blue ones are the question titles. There are 6 blue question titles in total.  This is exactly what they see in the drop-downs. They highlight their choice for each.  This is something we use currently - just in a different format for the question titles.

[Bill Sheboy]

Is that on the form "template" approach that JPD uses from the Create button?  If so, I have not used that yet so I'm unclear if/how those headings are used.

[Audrey Tolman]

Not at all.  Literally created a new field and manually added them in.  If I add a new field to add all the selected items up (not my formula), the total is correct.

[Bill Sheboy]

In that case, perhaps leave it as-is and test using it for entry, and the automation rule to calculate your measure.  If people have difficulty understanding the field options consider how to improve it.

[Audrey Tolman]

Will do.  Thanks so much for your help!

[Audrey Tolman]

Hi @Bill Sheboy 

We're getting errors on both.  Here are the screenshots.

[Bill Sheboy]

Audrey, I am having trouble following the thread here.  May we please stick with one of these rather than adding new threads?  Thank you!

Let's start with rule #2: would you please post what you pasted into the edit field for the edit?  If it was exactly what I provided then I suspect the smart values I guessed for the fields are incorrect. 

I cannot see your Jira instance, so you and the team will need to identify the correct ones, and probably use the custom field id values.  Here is a how-to article for finding the correct smart values: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

The steps are:

1. identify an issue (idea) which has your fields
2. using your Jira URL, lookup the supported fields for that example issue with a browser tab: https://<yourinstanceurl>/rest/api/2/issue/<yourExampleIssueKey>?expand=names
3. now search for the first field, Business Value, on the page
4. you will find something that looks like this: "customfield_10073": "Business Value"
5. use that customfield_10073 (or whatever it is) to replace "Business value" in that edit
6. repeat steps 3-5 for each of your fields
7. Now lets test this by adding a write to the audit log with one of your fields, such as {{customfield_10073.value.substringAfterLast("(").substringBeforeLast(")")}}
8. That step #7 should have written the number in parentheses after your selected option.

[Audrey Tolman]

Sorry about that.  Here's what was entered in the edit field

{{#=}} ( {{"Business value".value.substringAfterLast("(").substringBeforeLast(")")}} + {{"Balanced scorecard value".value.substringAfterLast("(").substringBeforeLast(")")}} + max({{"Risk mitigation value".value.substringAfterLast("(").substringBeforeLast(")")}}, {{"Opportunity enablement value".value.substringAfterLast("(").substringBeforeLast(")")}} ) ) / {{"Business readiness value".value.substringAfterLast("(").substringBeforeLast(")")}} {{/}}

[Bill Sheboy]

Thanks!  I see no typos in that one so it is probably the field names.  Let's try that how-to to replace the names with the custom field id values.

[Audrey Tolman]

Ok.  Will give it a try and will let you know.

[Audrey Tolman]

Bill,

Apparently, Product Discovery does not generate custom IDs for these fields.  This is how I created the fields.  

[Bill Sheboy]

From my perspective, JPD is just a team-managed project with a bunch of extra, fancy features :^)

When you add a field to a JPD project it is a custom field, and so you should be able to find them with that how-to I suggested.  I checked and the fields I added to my JPD project (to test the smart values I gave you) did have custom field id values that can be used in rules.

[Audrey Tolman]

ok he tried again and got the custom codes but still the same error.

here is the edit code:   
{{#=}} ( {{"customfield_10298".value.substringAfterLast("(").substringBeforeLast(")")}} + {{"customfield_10300".value.substringAfterLast("(").substringBeforeLast(")")}} + max({{"customfield_10301".value.substringAfterLast("(").substringBeforeLast(")")}}, {{"customfield_10302".value.substringAfterLast("(").substringBeforeLast(")")}} ) ) / {{"customfield_10304".value.substringAfterLast("(").substringBeforeLast(")")}} {{/}}

[Bill Sheboy]

Let's add some Log actions to write things to the audit log in the middle of the rule.  Hopefully that will pin things down:

• trigger: your trigger (no change)
• action: log for this value...This should write the selected value.

Here is the Business Value selection: {{customfield_10298.value}}

• action: log for this value...This should write the number/weight.

Here is the Business Value parsed: {{customfield_10298.value.substringAfterLast("(").substringBeforeLast(")")}}

• action: edit issue fields...to set BizJf (no change)

[Audrey Tolman]

We'll give it a try

[Audrey Tolman]

Bill,

We still don't have the calculation but here's a screenshot of the logs.

[Bill Sheboy]

It appears your Business Value field (customfield_10298) does not contain what you showed in the earlier images.  And so what I suggested to extract the weight from the quotation marks could not work.

Let's get this working for one field at a time: please post an image of the selectable options for the Business Value field.  With that the rule can be adjusted to use it.  If it is just a number that should be straightforward.

Then we can repeat that with another field to help your team understand how to update the remaining ones.

[Audrey Tolman]

Here is the information from the Business Value field that shows the options to choose from and the weighted value for each option.

Not all of my fields have number values as options.  I've also attached info on Time Criticality.

[Bill Sheboy]

Thanks for confirming with the images.  As your fields contain numbers and text, you can try pivoting to use lookup tables. 

For each field you would create a lookup table, where you add a row for each option: the key = your list option and the value = what you want used in your calculation.  For example:

Table name: tblBusinessValue

• key = No Revenue or Cost Savings Impact and value = 0
• key = 25,000 and value = 25000
• key = 50,000 and value = 50000
• key = 75,000 and value = 75000
• key = 125,000 and value = 125000
• key = 200,000 and value = 200000
• key = 1MM and value = 10000
• key = 2.5MM and value = 2500000

Please adjust the values as you want used in your calculations (e.g., switch the values to the Weights if you wanted).

Repeat this for each field to add a table.

Then your calculation would use a table value by getting the entry for the field:

{{tblBusinessValue.get(issue.customfield_10298.value)}}

[Audrey Tolman]

Bill,

Apparently that's not working either.  Are you open to having a call with my developer and I?

[Bill Sheboy]

Sorry, I am unable to do that currently, but I think you are quite close...

Once you have the tables set up, you can use your fields to get() the values to use in the calculations.  This is the example I gave for the Business Value:

{{tblBusinessValue.get(issue.customfield_10298.value)}}

And so please do the same thing for each value in your math expression.

formula:

{{#=}} ( {{tblBusinessValue.get(issue.customfield_10298.value)}} + {{tblBalancedScorecard.get(issue.customfield_10300.value)}} + max( {{tblRiskMitigation.get(issue.customfield_10301.value)}}, {{tblOpportunityEnablement.get(issue.customfield_10302.value)}} ) ) / {{tblBusinessReadiness.get(issue.customfield_10304.value)}} {{/}}

[Audrey Tolman]

ok, I think he tried that as well.

[Bill Sheboy]

Please confirm your table names match the calculations, as the two versions you are showing appear to be different.

For example, {{tblBalancedScorecard}} versus {{tblBalancedScorecardValue}}

[Audrey Tolman]

Looks like it's the same error.  Is this the correct formula?

[Bill Sheboy]

Some of your get() function calls are missing the closing parenthesis.  Specifically the ones for Risk Mitigation, Opportunity Enablement, and Business Readiness.

If you look at the first one for Business Value that is a good example.

[Audrey Tolman]

ok I'll have him update

[Audrey Tolman]

Still some errors.  Is the formula correct this time?

[Bill Sheboy]

That looks better. 

Would you please show the error message as I cannot see it in the screen image? 

And have you confirmed that each of the fields you are using has a value selected?

[Audrey Tolman]

Here's the error message.  Yes, I've confirmed each field is set up with a value.

[Bill Sheboy]

Wow; that says all the values collapsed to empty!

After all of your table creates, let's write this to the audit log:

The field= "{{issue.customfield_10298}}" and the field value= "{{issue.customfield_10298.value}}" and the table lookup=
"{{tblBusinessValue.get(issue.customfield_10298.value)}}"

If those first two are empty, that would explain why the lookup from the table is empty.

[Audrey Tolman]

Does this mean they aren't empty?  It's picking up the value of the option chosen but not the weighted score.  In addition, our other fields that just have text options to choose from, it's returning just the text.

[Bill Sheboy]

Progress!  That indicates the lookup from the table is not working: either the key is incorrect or the value is somehow converting to a number (i.e., removing the comma before the lookup attempt).

Would you please show an image of the action which creates the table for Business Value?

[Audrey Tolman]

Here you go

[Bill Sheboy]

Your lookup table has dollar signs in front of the numbers, and it appears you field configuration does not.  We can see this from the values that were written to the log.

Please change them to be the same for this field and try again.  If that works, we may have identified the issue for the different fields in the calculation.

[Audrey Tolman]

here they are

[Bill Sheboy]

Huh...I just tried this in both a Team-managed project (TMP) and a JPD project (which is based on TMP) and both worked for that lookup.

Please add this to the audit log after those first writes:

the lookup table: {{tblBusinessValue.entries}}

That should list the table, keys and values, so we can identify any differences.

[Audrey Tolman]

[Bill Sheboy]

That result does show what we expected, although it is unclear why the lookup's get() does not work.  When I tested this same scenario/rule structure in a JPD project it works.

The rule could be glitched from too many edits, so two things to check for that:

First, disable the rule.  Re-enable it, and then retest.  If it still fails...

Disable the rule, and recreate just part of it as a new rule: add the trigger, just one lookup table, and the writes to the audit log.  If it works, the rule can be built upon to continue.

If not, it is time to ask your Jira site admin to submit a ticket to Atlassian Support team at this link: https://support.atlassian.com/contact/#/  They may see something we are missing.

[Audrey Tolman]

Bill

SUCCESS!  Thanks for your help!!!

[Bill Sheboy]

Awesome; I am glad to learn it is working now!

What did it: the disable/enable, re-create the rule, something else?

[Audrey Tolman]

2 things.  we disable/enabled but also created a separate field to get the max value of the 2 fields in the equation.  Our new equation included the value in that field.  What a wild ride!

[Audrey Tolman]

You're exactly right.  We have a 2nd formula for WSJF where we take the BizJF value and divide by the weighted score associated with the T-shirt size.

Here are the calculations we currently use in ServiceNow for both.

BizJF

if  (Number(current.u_business_readiness.getValue()) !=0) {

current.u_bizjf  =  ((Number(current.u_business_value.getValue()) + Number(current.u_balanced_scorecard_value.getValue()) + Math.max(current.u_risk_mitigation.getValue(), current.u_opportunity_enablement.getValue()) + Number(current.u_time_criticality.getValue())) / }

else {

current.u_bizjf = 0;

}

WSJF

              Var val = (Number(current.u_business<value.getValue()) + Number(current.u_balanced_scorecard_value.getValue())) =

Math.max(current.u_risk_mitigation.getValue(), current.u_opportunity_enablement.getValue()) + Number(current.u_time_criticality.getValue())) /

(Number(current.u_business_readiness.getValue()) + Number(current.size.getValue()));

              Current_wsjf = wsjf = val;

This is the format I tried in Jira:

{{issue.Business Assessment Questions.business value.getValue + Balanced scorecard value.get + Value = {{max.risk mitigation value.getvalue or opportunity enablement.getvalue}}/business readiness.getValue}}

[Audrey Tolman]

Hi @Bill Sheboy 

We tried the automation and we received an error saying we have too many variables.  Is there a limitation or can you think of anything else to try?

[Bill Sheboy]

I have never seen that error before!  (Although I have seen an error for reaching the maximum number of components in a rule: 65.)

Would you please show an image of the error and your rule structure?  That may help clarify what is happening.  Based on your scenario I did not think you needed variables at all...just the issue fields on which to perform the math.

[Audrey Tolman]

Here are the screenshots from my developer.  He first tried with the values I had in the one field which didn't work.  So I created separate fields for each of the assessments.

[Bill Sheboy]

Thanks for those images!

If you review the error message, it shows the fields are either collapsing to null/empty values, or to the text values from the select-field list.  I wondered what JPD might provide for the fields, and...

I did some quick experiments and the weight values we enter for a JPD project field are not easily available to an automation rule.  So let's use another method to get what you need: lookup tables.

With a lookup table, we use the select-field options as a "key" and the weight as the "value", allowing us to lookup what you need for the calculations.  Then when you need a weight it would be this, providing a default value of zero:

{{tblBusinessValue.get("Business value".value)|0}}

Here is a revised rule to try:

• trigger: value changes for your fields (I would include all the inputs and the BizJf field to handle any manual tweaks.)
• action: create lookup table
  • name: tblBusinessValue
    • key: No Revenue of Cost Savings Impact (0)
    • value: 0
    • key: >= $25,000 (Revenue + Savings over 3 years) (1)
    • value: 1
    • ...repeat for other values
• action: create lookup table for tblBalancedScorecard...
• action: create lookup table for tblRiskMitigation...
• action: create lookup table for tblOpportunityEnablement...
• action: create lookup table for tblBusinessReadiness...
• ...more as needed
• action: edit issue to set the BizJf field

{{#=}} ( {{tblBusinessValue.get("Business value".value)|0}} + {{tblBalancedScorecardValue.get("Balanced scorecard value".value)|0}} + max( {{tblRiskMitigationValue.get("Risk mitigation value".value)|0}}, {{tblOpportunityEnablementValue.get("Opportunity enablement value".value)|0}} ) ) / {{tblBusinessReadinessValue.get("Business readiness value".value)|0}} {{/}}

If that looks too complicated, we could use the weights directly from the option names instead, as they appear to be added to the end in parentheses.  That rule would be this:

• trigger: value changes for your fields (I would include all the inputs and the BizJf field to handle any manual tweaks.)
• action: edit issue to set the BizJf field

{{#=}} ( {{"Business value".value.substringAfterLast("(").substringBeforeLast(")")}} + {{"Balanced scorecard value".value.substringAfterLast("(").substringBeforeLast(")")}} + max({{"Risk mitigation value".value.substringAfterLast("(").substringBeforeLast(")")}}, {{"Opportunity enablement value".value.substringAfterLast("(").substringBeforeLast(")")}} ) ) / {{"Business readiness value".value.substringAfterLast("(").substringBeforeLast(")")}} {{/}}

Please let me know if you have questions setting either of these up.

UPDATE on 30 Sept 2023: The solution which worked was lookup tables, with key values matching the custom field option values.  The second proposed solution (using substring functions) did not work because the desired "weight" values were no longer stored in the custom field option values.  In addition, the number of edits to diagnose and implement this rule caused it to "glitch" behind the curtain.  This is a more often occurring problem with the rule editor...so customers please take note.

[Audrey Tolman]

Bill,

We have SUCCESS!  Thanks for all of your help!

[Carlos Daniel Caballero Limon]

Were ser im here 

[Carlos Daniel Caballero Limon]

Cómo mi number Is 510 3852684