cancel
Showing results for
Search instead for
Did you mean:
See all
##### Top groups
Explore all groups

## Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user

Level 1: Seed

25 / 150 points

Next: Root

1 badge earned

## Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

## Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

## Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

# Jira Structure Formula: Aggregation function with IF

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

## 1 answer

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.

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?

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.

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

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.

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

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.

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

Alessandro C_ _ALM Works_ likes this

### Suggest an answer

Log in or Sign up to answer