Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Marketplace Apps & Integrations

How to log work and track time in Jira when a person is part of multiple teams

  The manager’s daily activities include a list of challenges to reach high levels of efficiency for their teams. Part of these challenges is related to how to deal with the worklog systems sin...

68 views 2 1
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you