nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

Accepted answers

Bill Sheboy

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

Here's a screenshot of the set up Custom formula.jpg

All comments

Bill Sheboy

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

Business Assessment Questions.jpg

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

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

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) {

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

Audrey Tolman

Here's a screenshot of the set up Custom formula.jpg

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.

MicrosoftTeams-image (23).png MicrosoftTeams-image (22).png

Audrey Tolman

Hi @Bill Sheboy

We're getting errors on both. Here are the screenshots. MicrosoftTeams-image (24).png MicrosoftTeams-image (25).png

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:

identify an issue (idea) which has your fields
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
now search for the first field, Business Value, on the page
you will find something that looks like this: "customfield_10073": "Business Value"
use that customfield_10073 (or whatever it is) to replace "Business value" in that edit
repeat steps 3-5 for each of your fields
Now lets test this by adding a write to the audit log with one of your fields, such as {{customfield_10073.value.substringAfterLast("(").substringBeforeLast(")")}}
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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-software

jira-software-cloud