Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Recurrent Formula derived from children's values

Raul Diez April 11, 2022

Hi community!

In a Jira structure, I am trying to create a recursive formula that derives the RAG status in a way that displays it deriving it from the children in this way:

derivedDeliveryRAG

  • If the issue has the deliveryRAG populated, then deliveryRAG
  • If exists a child with deliveryRAG = Red, then Red
  • If exists a child with deliveryRAG = Amber, then Amber
  • If exists a child with deliveryRAG = Green, then Green
  • If exists a child with deliveryRAG = Closed, then Closed
  • ELSE, derivedDeliveryRAG(Children)

I started with this, but as you can imagine, I had some problems, so therefore my question here. Can someone help me out?

At the moment, I achieved part of it with this:

IF (deliveryRAG = "Closed"; deliveryRAG;
deliveryRAG = "Green"; deliveryRAG;
deliveryRAG = "Amber"; deliveryRAG;
deliveryRAG = "Red"; deliveryRAG;
MAX#children{deliveryRAG})
IF (deliveryRAG = "Closed"; deliveryRAG;
deliveryRAG = "Green"; deliveryRAG;
deliveryRAG = "Amber"; deliveryRAG;
deliveryRAG = "Red"; deliveryRAG;
MAX#children{deliveryRAG})

2 answers

2 accepted

2 votes
Answer accepted
Raul Diez April 13, 2022

Hi David,

I solved it.

The DeliveryRAG is a field that can have the following values: "Closed", "Green", "Amber", "Red".

What I needed is to have in a field in the structure, that tells me what is the max value  of the subtree, let's call it "ChildrenRAG". I.e. if the DeliveryRAG of the children are "Green", "Green" and "Red", then the parent ChildrenRAG should be "Red".

I needed to do it in multiple phases (fields) as follows, where each block is a different column/field/formula:

//NumericRAG
IF ( deliveryRAG = "Closed"; 1;
deliveryRAG = "Green"; 2;
deliveryRAG = "Amber"; 3;
deliveryRAG = "Red"; 4;
0)

//ChildrenNumericRAG
IF (
numericRAG = 1; numericRAG ;
numericRAG = 2; numericRAG ;
numericRAG = 3; numericRAG ;
numericRAG = 4; numericRAG ;
MAX#strict{numericRAG}
)

// ChildrenRAG
IF (childrenNumericRAG = 0; "None";
childrenNumericRAG = 1; "Closed" ;
childrenNumericRAG = 2; "Green" ;
childrenNumericRAG = 3; "Amber" ;
childrenNumericRAG = 4; "Red";
"Green")

David Niro
Atlassian Partner
April 13, 2022

Hi @Raul Diez !


Well done!! Thank you for sharing!!!

Like Dave Rosenlund _Trundl_ likes this
Harshal Bhave January 15, 2023

But there is currently no way to self reference column in structure I believe. 

1 vote
Answer accepted
David Niro
Atlassian Partner
April 12, 2022

Hello @Raul Diez ,

David from ALM Works here.

Is the goal to have the colors match the RAG status, or just to have the text "Red", "Amber", "Green"?

If the former, this functionality is not yet available, but we are working on it!

If the latter, could you share a bit more about the deliveryRAG field?  Is it a Select List (single choice) custom field, or is it a custom field that comes from another app?  

I look forward to your response.

Best,
David

Suggest an answer

Log in or Sign up to answer