Recurrent Formula derived from children's values

Question

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

David Niro · Accepted Answer

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

Raul Diez · Accepted Answer

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