ALM Structure Formula to display earliest due date of subtree items

[Vye Alexander]

I'm trying to set up a Function column on a Structure view that will:

1. For each sub-issue under a root issue, display Wiki Markup on whether it is on track, due soon, or overdue based on its Due Date (got this part working)
2. For each root issue, display Wiki Markup on whether the sub-issue with the soonest due date is on track, due soon, or overdue (can't figure this part out)

Basically I want to roll up the most urgent due date's Wiki Markup to the root issue. Is this possible? Would love some sage wisdom.

Here's my Expr for part 1, and my most recent failed attempt at part 2, if it helps. The root issue type is Request, and its subtree is everything linked to that Request issue or its sub-issues by certain link types. 

IF (issuetype != Request;

    IF (dueDate < today(), "{color:red}OVERDUE{color}", 
        DAYS_BETWEEN(today(), DueDate) <= 14, "{color:blue}Due Soon{color}", 
        DAYS_BETWEEN(today(), DueDate) > 14, "{color:green}On Track{color}", 
        dueDate = undefined, "{color:orange}Needs Due Date{color}",
        "{color:white}{color}"
    )
)
or IF (issuetype = "Request ";
    WITH x = MAX#subtree{due_date}:
    IF (x, "{color:black}x{color}") 
)

 Which is currently producing this: 

[Dima Kuzmin _ALM Works_ · Accepted solution]

Hi Vye,

Dmitrii with ALM Works here.

Please try the Formula below. I've used CONCAT and FORMAT_DATETIME functions to roll up the most urgent due dates to the root issue and Wiki Markup it according to your colour scheme.

I've also used OR operators for min#subtree portion of the code to make the conditions more clear and visible, but you can replace ORs with commas, just like you did in your Formula, and it will work as well.

Hope it fits!

Kind regards,
Dmitrii
ALM Works

IF (

issuetype != "Request";

if (
duedate < today(), "{color:red}OVERDUE{color}",
DAYS_BETWEEN(today(), duedate) <= 14, "{color:blue}Due Soon{color}",
DAYS_BETWEEN(today(), duedate) > 14, "{color:green}On Track{color}",
duedate = undefined, "{color:orange}Needs Due Date{color}",
"{color:white}{color}"
)
)

OR

IF (

issuetype = "Request";
WITH x = min#subtree{duedate}:

if (
x < today(), CONCAT("{panel:bgColor=red}", FORMAT_DATETIME(min#subtree{duedate}, "dd/MM/yy"),"{panel}")
)

or

if (
DAYS_BETWEEN(today(), min#subtree{duedate}) <= 14, CONCAT("{panel:bgColor=blue}", FORMAT_DATETIME(min#subtree{duedate}, "dd/MM/yy"),"{panel}")
)

or

if (
DAYS_BETWEEN(today(), min#subtree{duedate}) > 14, CONCAT("{panel:bgColor=green}", FORMAT_DATETIME(min#subtree{duedate}, "dd/MM/yy"),"{panel}")
)

or

if (
min#subtree{duedate} = undefined, "{color:orange}Sub-issues need Due Dates{color}"
)

)

[Vye Alexander]

Dmitrii, Dave, thank you for the response! I tried the above code and it doesn't quite work - all the root issues have that AGGR error like before. After some troubleshooting, I was able to determine that the part it is upset about is the min#subtree{duedate} part. Just to test, I subbed in today() for x and was able to get rid of the AGGR error but, obviously, that's not the value I need for x to make it do what I want.

Here is the change I made from the code above to stop the AGGR error:

IF (

issuetype != "Request";

if (
duedate < today(), "{color:red}OVERDUE{color}",
DAYS_BETWEEN(today(), duedate) <= 14, "{color:blue}Due Soon{color}",
DAYS_BETWEEN(today(), duedate) > 14, "{color:green}On Track{color}",
duedate = undefined, "{color:orange}Needs Due Date{color}",
"{color:white}{color}"
)
)

OR

IF (

issuetype = "Request";
WITH x = today():

if (
x < today(), CONCAT("{panel:bgColor=red}", FORMAT_DATETIME(x, "dd/MM/yy"),"{panel}")
)

or

if (
DAYS_BETWEEN(today(), x) <= 14, CONCAT("{panel:bgColor=blue}", FORMAT_DATETIME(x, "dd/MM/yy"),"{panel}")
)

or

if (
DAYS_BETWEEN(today(), x) > 14, CONCAT("{panel:bgColor=green}", FORMAT_DATETIME(x, "dd/MM/yy"),"{panel}")
)

or

if (
x = undefined, "{color:orange}Sub-issues need Due Dates{color}"
)

)

And here is what it produces now:

Any ideas why min#subtree{duedate} wouldn't work? Thanks!

[Nicholas Ellis _ALM Works_]

min{duedate} should work by itself, the #subtree isn't needed.  If it is still giving you the AGGR? error, I would reach out to our support team support@almworks.com

[Vye Alexander]

Okay, got everything working! A couple notes:

1. The issue where #subtree was causing an AGGR? error seems to be related to the version of Structure. We were using version 6.0, but have now updated it to 6.4.0.
   
   • I think removing the #subtree, as Nicholas suggested, also worked to remove the AGGR? error (I'm pretty sure I tried it before the upgrade was applied) but that wouldn't have solved the next thing...
2. The MIN{} function would return the root's due date if it was the earliest of the bunch, and I wanted only the sub-items. I switched to using the #strict modifier and that seems to have done the trick!

My final, functioning Expr and Wiki Markup looks like so:

IF (

issuetype != "Request";

if (
duedate < today(), "{color:red}OVERDUE{color}",
DAYS_BETWEEN(today(), duedate) <= 14, "{color:blue}Due Soon{color}",
DAYS_BETWEEN(today(), duedate) > 14, "{color:green}On Track{color}",
duedate = undefined, "{color:orange}Needs Due Date{color}",
"{color:white}{color}"
)
)

OR

IF (

issuetype = "Request";
WITH x = min#strict{duedate}:

if (
x < today(), CONCAT("{panel:bgColor=red}{color:white}Task Overdue{color}{panel}")
)

or

if (
DAYS_BETWEEN(today(), x) <= 14, CONCAT("{panel:bgColor=blue}","{color:white}", "Task due ", FORMAT_DATETIME(min#subtree{duedate}, "dd/MMM/yy"),"{color}","{panel}")
)

or

if (
DAYS_BETWEEN(today(), x) > 14, CONCAT("{panel:bgColor=green}","{color:white}Tasks On Track{color}","{panel}")
)

or

if (
x = undefined, "{panel:bgColor=orange}{color:white}Tasks missing due dates{color}{panel}"
)

)

 Which results in this:

Thanks, everyone, for your help! 

[Dave Rosenlund _Trundl_]

@Vye Alexander: For your benefit as well as others who have viewed this thread, ALM Works will be hosting a Structure 7 / Formulas / Expr 2 Bootcamp. 

Thought you might be interested.

Best,

-dave 

[Kenny Baker]

@Dave Rosenlund _Trundl_ 

Is this still something being planned or offered?

[Dave Rosenlund _Trundl_]

Welcome to the community, @Kenny Baker  👋

We may do it live again if there's demand enough. Meanwhile, the previous session was recorded if that helps?

-dave

[Kenny Baker]

Nice! Thank you for the link to the Vod!

[Dave Rosenlund _Trundl_]

You're welcome, @Kenny Baker.

[Kenny Baker]

@Dave Rosenlund _Trundl_  I'm using your suggestion as a base for what I'm trying to do for my teams. But instead of checking the Child Issue dates against today, I was hoping to instead fire off a wiki Markup alert on the Parent issue if...

- The latest DueDate on Child Issues Is Earlier than the Parent DueDate
- The latest DueDate on Child Issues Is Later than the Parent DueDate

Thanks in advance, this is great stuff!

[Dave Rosenlund _Trundl_]

@Nicholas Ellis _ALM Works_  or  @David Niro,  can one of you answer @Kenny Baker's question?

[Nicholas Ellis _ALM Works_]

Hi Kenny,

This should be pretty simple to do.  Here is some code to get you started.

with diff_days = DAYS_BETWEEN( MAX{due_date}, due_date) :

IF diff_days > 0 : "max child due date early"
ELSE IF diff_days < 0 : "max child due date late"
ElSE IF diff_days = 0 : "child due_date matches parent"
  

You should simply be able to replace the text with wiki markup as you see fit to communicate this information.  Here is the wiki markup documentation in case you need it.

Cheers,
Nick

[Dave Rosenlund _Trundl_]

Hi, @Vye Alexander. Welcome to the community. It looks like you are off to an awesome start with Structure Formulas!

I've asked one of my colleagues to take a closer look and suggest a fix.

Stay tuned 👍🏻

-dave