Jira Structure Formula: Aggregation function with IF

[Antonio Prioletti]

HI guys, I'm trying to write a structure formula where the value to aggregate has some conditions: 

with jira_weeks=JIRA_WEEKS(Estimate):
with sum_from_today = DAYS_BETWEEN(DATE_ADD(ActStart, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days"), TODAY())>0 AND !DEFINED(ActEnd):
with act_start = IF(sum_from_today; TODAY(); ActStart):
MAX{
IF(jira_weeks>=1; DATE_ADD(act_start, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days");
DATE_ADD(act_start, CEILING(JIRA_DAYS(TimeSpent+RemainingEstimate)), "days"))
}

Any suggestion?

What I'm try to achieve here is to compute the estimated end as either the sum of the actual start with the spent+remaining or, if this is already over today's date, computing it as today + spent + remaining

[Alessandro C_ _ALM Works_]

Hello Antonio, Alessandro from ALM Works here.

Thanks for posting your question!

From what I could understand of the formula, the calculation is already providing the estimated completion date in both cases. I've just made some syntax adjustments to make sure it doesn't throw you any errors.

Other than that, I didn't change it too much, aside from moving one condition for the ActEnd, to not calculate anything, if it is not empty. That's what you wanted to achieve I suppose?

I believe this already leaves room for improvement in case you want to tweak it a little further. In any case, nice example you have there, thanks for sharing!

Below you have my tweaked version. Hope it helps

{code}

with jira_weeks=JIRA_WEEKS(Estimate):
with sum_from_today = DAYS_BETWEEN(DATE_ADD(ActStart, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days"), TODAY()):
with act_start = IF(sum_from_today; TODAY(); ActStart):
MAX{
IF(!ActEnd;IF(jira_weeks>=1; DATE_ADD(ActStart, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days");
DATE_ADD(ActStart, CEILING(JIRA_DAYS(TimeSpent+RemainingEstimate)), "days")))
}

{code}

If you have any additional questions or comments, please do keep posting. I'll be happy to help you out!

Best regards,
Alessandro C.

[Antonio Prioletti]

Hi Alessandro, 

thanks much for the answer! Actually, looking at your code, I saw that in `DATE_ADD` you used ActStart instead of the defined variable act_start that is exactly the variable helping to decide which date should be used as starting point and, that, was causing the formula to don't work :( Any suggestion? 

[Alessandro C_ _ALM Works_]

Hi Antonio,

Thanks for clarifying. For a moment I understood it was a typo from the original variable.

I was able to make it work replacing the MAX {} curly brackets with common parenthesis like MAX() and the whole expression in it.

Could you check if that works as you expect?

Best Regards,
Alessandro C.

[Antonio Prioletti]

HI Alessandro,

I can definitely try it out. But, using MAX() instead of MAX{}, wouldn't change the actual functionality? Since MAX{} will use the aggregation and MAX() instead is computing "just" the MAX?

Thanks,

Antonio

[Alessandro C_ _ALM Works_]

Hi Antonio,

Thanks for trying that.

My understanding is that since the calculation is being done based on values of the current row, the aggregate function won't be able to choose from children of the calculated values, that's what I suspect of the error being thrown when using the aggregation, but I may be wrong.

But if my understanding is correct, using the default MAX() function will do the calculation based on the referenced cell Actual Start date and trigger no errors.

Still, if that doesn't bring the result you are expecting, let me know and we will keep investigating why the aggregation is throwing out errors.

Best regards,
Alessandro C.

[Antonio Prioletti]

Hi Alessandro,

so, using the MAX does the right thing for the children but, obviously, doesn't work for the parent since no aggregation will be performed. The MAX here was supposed to compute the estimated end for the parent tasks based on the max of the children.

Thanks,

Antonio

[Alessandro C_ _ALM Works_]

Hi Antonio,

You are right about the MAX Aggregate. I have got back to the drawing board to figure out how to address that and I believe I found a way to make your formula work.

In short, what I did was to condense the "sum_from_today" and "act_start" variables in a different formula field and use them as a single variable in the main formula.

In details, here's what I did:

1. Reduce the main formula to the following:
   with jira_weeks=JIRA_WEEKS(Estimate):
   

   MAX{
      IF(jira_weeks>=1;
         DATE_ADD(act_start, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days");
         DATE_ADD(act_start, CEILING(JIRA_DAYS(TimeSpent+RemainingEstimate)), "days")
      )
   }

   This will make Structure complain that the "act_start" variable is missing. So what you can do next is:
2. Click the 'act_start' variable to assign a new value
3. Select "Formula" from the list
4. Insert the variables in this additional Formula field
   

   with sum_from_today = DAYS_BETWEEN(DATE_ADD(ActStart, CEILING(7*JIRA_WEEKS(TimeSpent+RemainingEstimate)), "days"), TODAY())>0 AND !DEFINED(ActEnd):
   
   IF(sum_from_today; TODAY(); ActStart)

5. Adjust the Start and End dates field

I was able to make it work using the original formula you have posted, hope it works for you as well.

Last but not least, if the issue does persist, I would suggest contacting our support team (by email or through our website) so we could go over the issue in a call may help to put a bit more context to the issue.

Regardless, hope the suggestion above helps and will be happy to continue being of assistance in case it doesn't.

Kind regards,
Alessandro C.

[Antonio Prioletti]

Hi Alessandro,

the suggestion indeed it works! Fantastic, I wasn't aware about the necessity of defining the variables as "formula" instead of `with variable a=...` syntax to enable having aggregation "enabled". Very appreciated.

Thanks,

Antonio