ALM Structure: Merge several values into one child of a process in one column.

[Jochen Berdi]

Hello

We use Structure and are thrilled.
I have reached my limit with the following requirement:

In our Structure we have issues including their child of issues.
BSP
Issue A
Issue B is child of issue A
Issue C is child of issue A
Issue D is child of issue B and so on.

One column in our structure (name number) contains numbers (1-3) based on a formula.
Issue A 
Issue B 1
Issue C 3

Now I want to query and visualise the following in another formula column.

I would like to display the highest value of the Number column of all children in the new Formula Column and also display the issue key including the link to the issue.

Result in the new formula column 3 / hyperlink issuekey from issue C (like 3 / DMD-123).

I get the maximum value from Column Number with
//ChildrenNumericRAG
if(issuetype="Issue A";MAX#children{numericRAG})

But I don't know how to get from the at most match to the associated issuekey (link) to maybe use theMAP(""[${$.key}|${$.url}]""")

And what if the maximum value in Column Number occurs several times, then it would be great if the result looked like this:

3 / DMD-123, DMD456

Is this possible?

Best Regards 
Jochen

[Nicholas Ellis _ALM Works_]

Hi Jochen,

This one is deceptively complex, but the good news is you don't have to construct the URL yourself.  All you have to do is set the output to wiki markup and it will automatically make the keys into links.  Here is the code:

with max_num = MAX{numericRAG} :
with keys = VALUES#strict{key} :
with index = VALUES#strict{numericRAG or 0}.index_of(max_num) :

IF max_num and index >= 0:
  max_num.CONCAT(" / ", keys.get(index))
ELSE :
  "no values found"

First we find the max value, #strict means looking down the hierarchy to children and grandchildren etc, but excluding the current row.  Then we get a list of keys, and then we get what should be an identical list of story points (inserting 0 to replace nulls).  Then we check that the max value and the index exist, and return the max value, " / ", and the key at the same index as the max value.

Also if we had multiple values with a tie for the max we would find the max higher in the hierarchy first, so we might miss that the same value exists deeper in the hierarchy.

Cheers,
Nick [ALM Works]

[Jochen Berdi]

Hi Nick

thanks, the macro fits except for one thing. If max_num occurs several times, only the first hit appears in the result.

3 / DMD-123 and not
3 / DMD-123, DMD-456

regards

Jochen

[Dave Rosenlund]

By the way, @Jochen Berdi, it is *awesome* to hear things like, "We use Structure and are thrilled."

Trust me, words like that put a smile on a lot of the Structure team's faces.

Thank you!

-dave

[Nicholas Ellis _ALM Works_]

Hi Jochen,

Here is an improved version of the formula to handle multiple max values better.

WITH maks = array#subtree{FE}.MAX():
WITH all = array#subtree { array(FE, item) }:
WITH res = all.FILTER($.GET(0) = maks).flatten().FILTER($.GET(0) != maks):
WITH count_si = COUNT#truthy{FE}:
IF count_si: CONCAT(maks; " / "; res)
ELSE IF !maks: "No Value"

 Here maks is the max, and res is the rest of the issues, if there are any other "maxes".

Cheers,

Nick [ALM Works]

[Jochen Berdi]

Hello Nick,

Unfortunately, I have too little knowledge to integrate the addition into the existing formula. I am sorry. Learning by doing.

Best regards
Jochen

[Nicholas Ellis _ALM Works_]

Hi Jochen,

This formula does not need to be incorporated into the old one.  It should work alone simply copy and paste it as is.

Cheers,

Nick [ALM Works]

[Jochen Berdi]

Hi Nick,

my mistake. It works perfekt. Thanks a lot.

Best Regards
Jochen