Complex calculated text custom field

[Antônio Duarte]

Hi, @fcarmario ,

I ask for help with a calculated text custom field, whose formula is a little bit complex. In my project I have A LOT OF issues of issue type RISK. This issue type has three custom fields: 1) Numeric field "Business Impact", filled in by the user with a floating point number; 2) Numeric field "Development Impact", filled in by the user with a floating point number; 3) Text field "Quadrant", calculated by JWT following these steps:

Step 1: among ALL the RISK issues of the project, identify the smaller and the bigger value for "Business Impact" (lets name them BI_small and BI_big); Step 2:calculate BI_average as (BI_small + BI_big) / 2 Step 3: among ALL the RISK issues, identify the smaller and the bigger value for "Development Impact" (lets name them DI_small and DI_big); Step 4: calculate DI_average as (DI_small + DI_big) / 2 Step 5: Finally, calculate the Quadrant value for each RISK as: if (Business Impact < BI_average and Development Impact < DI_average) then Quadrant = "I"; if (Business Impact < BI_average and Development Impact >= DI_average) then Quadrant = "II"; if (Business Impact >= BI_average and Development Impact < DI_average) then Quadrant = "III"; if (Business Impact >= BI_average and Development Impact >= DI_average) then Quadrant = "IV";

Is it possible to accomplish this task with JWT?

Thanks in advance.

Antonio.

[Fidel Castro Armario]

Hi @acelsoddbr,

Yes, it's technically possible to do it, but it will be highly inefficient to implement it as a "Calculated Text Field".

There is a plausible implementation if we can:

1. Use post-functions in a transition to calculat Quadrant. We can use a reflexive global transition called "Calculate Quadrant" that we should execute manually whenever we want to recalculate the value of field Quadrant.

2. We can reduce the number of issues implied in the calculation of BI_average and DI_average, for example by using only non-closed or non-resolved issues, instead of all issues in the project.

Is it possible to accept both conditions I have described?

[Antônio Duarte]

Hello, @fcarmario, 1. A good place to put such post-function is in the Create Issue transition, because the values used in calculation will be typed-in by the user, usually at this moment, when creating a new RISK issue. BUT, the user can change such values for ANY RISK issue, at ANY time. Every time this happens, this calculation should take place to all RISK issues (because all of them have their own Quadrant field). The idea of a reflexive transition is a good one, but note that this recalculation should be executed for all RISK issues, and not only the issue where i clicked on the reflexive button. 2. The reduction of the amount of issues doesn't work exactly like you suggest. The workflow for the RISK issues in the project has only two states: Active and Inactive. Once created, a RISK remains "alive" during all this project and they will be referred by my TEST CASE issues by a special Issue Link type, and nothing else. One TEST CASE will refer to only ONE RISK, but one RISK can be referred by several TEST CASEs. We estimate that we will have no more than 50 RISK issues on every project (typically), so I guess that this reduction is not so crucial. Anyway, we can consider as "valid" RISK issues for calculation, all issues in the state "Active". 3. Note that every time a new RISK is created (or every time a RISK has such values changed), the calculation of the Quadrant must be done again for all RISKs, because clearly the average values will change. So, only as an example, it is possible that an existent RISK that belongs to Quadrand II, will have its Quadrant changed to III when a new RISK is created with appropriate values that changes the average values. Greetings. Antonio.

[Fidel Castro Armario]

Hi @acelsoddbr,

It can be done. I explain you the solution:

First of all, I recommend that field "Quadrant" is a Select List or a Radio Button with options I, II, III and IV, instead of simple Text field. This will allow you to generate table-like or 2D reports.

1) We create a global reflexive transition called "Calculate Quadrant" in Risk's workflow with the following configuration:

1.1) Add condition "Transition is triggered by JIRA Workflow Toolbox post-function", since we are going to trigger this transition automatically, but if you want you can't avoid this step, and this way the transition will be available to users.

1.2) Add the following 3 post-functions:

Post-function's 1 and 2 are "Mathematical and date-time expression calculator" with the following formulas:

avg([min(fieldValue({bbbbb}, issuesFromJQL("issuetype = 'Risk' AND  project = " + %{00018} + " AND status = 'Active'"))), max(fieldValue({bbbbb}, issuesFromJQL("issuetype = 'Risk' AND  project = " + %{00018} + " AND status = 'Active'")))]))

and

avg([min(fieldValue({ddddd}, issuesFromJQL("issuetype = 'Risk' AND  project = " + %{00018} + " AND status = 'Active'"))), max(fieldValue({ddddd}, issuesFromJQL("issuetype = 'Risk' AND  project = " + %{00018} + " AND status = 'Active'")))]))

Post-function 3 is Set a field as a function of other fields with the following setting rules:

[{bbbbb} < {00058} AND {ddddd} < {00059}]I
[{bbbbb} < {00058} AND {ddddd} >= {00059}]II
[{bbbbb} >= {00058} AND {ddddd} < {00059}]III
[true]IV

You should replace bbbbb with Business Impact field code, and ddddd with Development Impact field code.

2) You should also create another global reflexive transition called Edit Impacts in Risk's workflow with the following configuration:

2.1) Create a screen for this transition containing fields Business Impact and Development Impact, and associate the screen to the transition.

2.2) Remove fields Business Impact and Development Impact from edit screen, so that these two fields will only be editable through Edit Impacts transition.

2.4) Add the same 3 post-functions described for transition "Calculate Quadrant".

2.3) Add post-function "Write field on issues returned by JQL Query or Issue List" to Edit Impacts transition with the following configuration:

where JQL query is:

issuetype = 'Risk' AND project = %{00018} AND status = 'Active' AND issue != %{00015}

The configuration of this transition will be like this:

3) Add the same 4 post-functions described for transition "Edit Impacts" to transition "Create Issue". Beware to insert those 4 post-functions after transition "Creates the issue originally".

[Antônio Duarte]

All my texts here are not formatted correctly, but here you have my report:

I guess I made something wrong. Only the “local” Quadrant field is changed, but all the Quadrants of the other Risks, are not changed. Look: The Risk workflow is:

For the “Calculate Quadrant” reflexive transition: The Condition is:

And the post-functions are:

And for the “Edit Impact” reflexive transition, the post-functions are:

So, I’m guessing I made some mistake with the post-function number 4 above.

[Fidel Castro Armario]

Hi @acelsoddbr,

I don't find any error in your configuration. Execution of those transitions will take same time (1 second per transition or maybe more).

Please, try it on a project with a very little number or Risk issues. For example, 3 risks.

[Antônio Duarte]

Hi, @fcarmario ,

Not working. See below:

All the risks were set to "Business Impact" to zero and "Development Impact" to zero. As expected, all risks should be at Quadrant IV:

This is confirmed by my Excel worksheet that gives me the same calculations:

Now, if I set the first Risk to values 10 and 10, it remains at Quadrant IV, but all the others should go to Quadrant I, but this doesn't occurs, even after several seconds:

But notice that the Excel worksheet changes correctly:

I'm guessing I'm making something wrong on the post-function that should start the triggered reflexive transition! Is the "Source Value" parameter exactly the name of the reflexive transition (Recalcular Quadrante) as I configured it?

Thanks. Antonio.

[Antônio Duarte]

An extra info: after the steps above, if I enter in one of the other issues and click on the button to change the Impact values, even if I do NOT change anything, and only click on the button to update the values, then the Quadrant of THIS issue goes correctly to "I" as expected.

[Fidel Castro Armario]

Hi @acelsoddbr,

Your screenshots have quite low resolution, and I can barely read them, but I think that you are writing satus instead of status in the JQL query parameter in 4th post-function of transition "Alterar Risco Produto".

Please, edit your JQL query and write status.

[Antônio Duarte]

Hi, @fcarmario , My fault. You are positively right! I apologize for this big mistake! I thank a lot for your valuable help. Your solution implements EXACTLY our client needs. I've just read about the fusion of JWT with DECADIS. I wish you all the success with this new business challenge and I hope DECADIS preserve all your attention and professionalism while helping JWT users! Just a question: can I keep using this channel to create future support tickets for JWT? Can we count on still having your direct support to deal with these tickets? Thanks. Antonio.

[Fidel Castro Armario]

Hi @acelsoddbr,

Thank you for your good wishes and kind words. We have still to set the new support channels. I think we will migrate to JIRA as issue tracker and confluence for documentation and knowledge base.

I will collaborate with the support team, and will keep giving support, especially for complex questions.

[Antônio Duarte]

OK. I'm changing this ticket to Resolved. Thanks again. Antonio.

[Antônio Duarte]

Closed with 'OK' Resolved issue code.