JIRA structure Formula - Calculate with variable

Marc Schroff_77be4u

Good day.

I have the challenge. I want to calculate a value in the JIRA structure on four JIRA fields based on the following formula:
(Field 1 + Field 2 + 'Priority') / 'Budget') = Calculated number.

The input consists of:

• Two number fields : Field 1 and Field 2 (1,2,3...100)
• 'Priority': Blocker, Critical, Major, Medium, Minor, Trivial (Always present, so cannot be empty)
• 'Budget': Empty to > 5'000'000 (number)

Priority

For the 'Priority' I want to set a number which is then used in the above formula, depending on issue priority. (So if an issue has a priority 'Blocker', 13 should be used for the calculation). 

• Blocker=13,
• Critical=8,
• Major=5,
• Medium=3,
• Minor=2,
• Trivial=1

Budget

For 'Budget' a number is to be set from a range from-to:

• Empty=0,
• 0 - 5000 = 1,
• 5001 - 10000 =2
• 10001 - 50000 = 3
• 50001 - 200000 = 5
• 200001 - 1000000 = 8
• >=1000001 = 13

 

Yes I know, this is a WSJF formula and there are heaps of plugins, but we are not allowed to install plugins, respectively the onboarding process goes over 1-2 years.

So I hope you can help me to script a custom formula. Thanks for your help.

David Niro

Hello @Marc Schroff ,

David from ALM Works here.  Welcome to the community!
I had fun working on this, so thank you for that!  One thing to note is I did create the solution below in Structure 7.4.  If you have trouble with it, please check which version of Structure you are on and we can see what adjustments may be required.

What we are going to do is use formulas to create values for your Priority and Budget and then use those formulas as variables within our main formula.  
Because we cannot divide by 0, I have presented two potential options below.  The first will return a blank value when the Budget formula returns a 0 and the second will return a 0.

Main Formula:

option 1 (return blank)

IF(Budget_Formula > 0;
(number_field1+number_field2+priority_formula)/Budget_Formula)

option 2 (return 0)

IF(Budget_Formula > 0;
(number_field1+number_field2+priority_formula)/Budget_Formula;
0)

At this point, your formula will have four Red variables to indicate that it does not know what you want to point to.  The next step is to map these variables.

number_field 1 & number_field 2:

for both of these you click on the variable name and map them to the field you are using to enter the values.  To speed up the process of finding them in the drop down list, you can start typing the field name.

For the other two variables, you will want to select "Formula" from the list of variables available.  I have provided the formulas you will want to use below.

Priority Formula:

If(priority = "Blocker";13;
priority = "critical";8;
priority = "major";5;
priority = "medium";3;
priority = "minor";2;
priority = "trivial";1)

Budget Formula:

if(budget = empty;0;
budget = 0 OR budget > 0 and budget < 5001;1;
budget >5000 and budget <10001;2;
budget >10000 and budget <50001;3;
budget >50000 and budget <200001;5;
budget >20000 and budget <1000001;8;
budget > 1000000;13)

Please let me know if this helps, or if you have any questions!

Best,
David