Jira Date calculation in automation using date and number custom fields

[Eyal Taylor]

Hi, i have set up a date calculation based on input date and number fields which works in a workflow transition.

I am trying to run this same calculation on all historical tickets, and am therefore trying to a manual automation with JQL query.

The automation is failing with error:

Error rendering smart-values when executing this rule:

Failed to get value for issue.fields["Start date"] | date('businessAdd', (issue.fields["Story Points"] + (issue.fields["Tester Story Points"] - 1)/2) , "days") | date: {{issue.fields["Start date"] | date('businessAdd', (issue.fields["Story Points"] + (issue.fields["Tester Story Points"] - 1)/2) , "days") | date }}

Smart Value calculation:

{{issue.fields["Start date"] | date('businessAdd', (issue.fields["Story Points"] + (issue.fields["Tester Story Points"] - 1)/2) , "days") | date }}

Can anyone please help?

[Eyal Taylor · Accepted solution]

Thanks Bill for the direction.

I have been trying to debug and test small changes. I am at a point where the automation rule is completing with success on a specific ticket where i am running the rule manually, and yet the custom field is not being populated with any value at all.

I have tried the following as a basis.

{{(issue.Start Date)}}

i.e to copy the date from custom field "Start Date" to custom field "Estimated Delivery Date"

The automation completed successfully, yet no value is set for the field. I have tried a few other formulas that complete successfully, but no value is set for the field.

Do you have any advice just to get the ball rolling where i might be going wrong? 

Thank you.

[Bill Sheboy]

First thing, please try to stay with one thread when responding.  That will help people reading this question in the future know if there are multiple solution approaches to try.  Thanks!

Your expression has unnecessary parentheses inside.  It should be like this, correcting the case of the smart value:

{{issue.Start date}}

Also note Start Date is one of those fields which can appear as text to a rule rather than as a date type.  And so if the above expression does not work, please use this to force the value to interpret correctly:

{{issue.Start date.toDate}}

[Eyal Taylor]

Bill, 

Thanks so far.

I have got to a point where i am able to get a result for the start date plus story points.

{{issue.Start Date.plusDays(issue.story points)}}

However when i add the next element of adding Tester Story Points in to the equation the rule is finishing with 'Success' and the field is not populated with anything.

{{issue.Start Date.plusDays(issue.story points + issue.Tester Story Points)}}

As a reminder, this is the calculation i am trying to achieve.

Start Date Field + (Story Points Field + Tester Story Points Field - 1) / 2 

I am not sure however, how to complete the second part of the calculation in one part and add the result as a DATE to the 'Start Date' field.

I cannot understand from the documentation how to do a calculation and then convert that to adding the result to a date field.

I am aware i need to complete this calculation first, then add it to the start date.

(Story Points Field + Tester Story Points Field - 1) / 2 

It is also worth noting, i want to add it as Business Days and not just adding Days.

Would be very grateful if you could help me further on this.

[Bill Sheboy]

Before proceeding, please confirm you have the correct smart values (or custom field ids) for your custom fields.  Smart values are name, spacing, and case-sensitive.  When an incorrect one is used, it evaluates to null.  To find the correct ones, please use this how-to article: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

Next, the way to learn how smart value expressions work is to write them to the audit log with the Log action.  This will help you test and observe if the values are as expected.

Assuming your smart values are correct...

There are two forms of math expressions for smart values: inline and long format: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

You could try the inline one, with the plus() and divide() functions.

{{issue.Start date.plusDays(issue.Story points.plus(issue.Tester Story Points.minus(1)).divide(2))}}

Please note well: inline, math expressions typically preserve the number typing, and so if the values are integers they may truncate to zero after division.  This may be less relevant for your case as the parameter to plusDays() is also an integer.

Regardless, ensure you fully test with examples to confirm the behavior.

[Eyal Taylor]

Dear Bill,

Thank you. That is perfect, and now i understand how to build these properly.

I just added 'round' to ensure the value is rounded up so it does not break the calculation.

{{issue.Start date.plusDays(issue.Story points.plus(issue.Tester Story Points.minus(1).divide(2)).round)}}

Really appreciate the help!

[Bill Sheboy]

Awesome; I am glad to learn it is working!  Please consider marking this question as "answered" to help others with a similar need find solutions faster.  Thanks!

[Bill Sheboy]

Hi @Eyal Taylor -- Welcome to the Atlassian Community!

For a question like this, context is important for the community to help.  Please post the following:

• what type of project is this (e.g., company-managed, team-managed, etc.), 
• an image of your complete automation rule,
• images of any relevant actions / conditions / branches,
• an image of the audit log details showing the rule execution, and
• explain what is not working as expected.

Until we see those...

The syntax you are using is not correct for math expressions and date values.  Please review the linked documentation to learn more.

And, please explain what your expression is trying to do, as that will help the community to offer suggestions.  For example, it appears you are trying to do this:

GIVEN an issue with a Start Date value
AND a Story Points value
AND a Tester Story Points value
WHEN field X is empty
THEN set field X by incrementing the Start Date in business days units using a weighted sum of the Story Points and Test Story Points fields

Finally, I recommend discussing what you are trying to do with your team's scrum master / agile coach as it may not align with the teams practices.

Kind regards,
Bill

[Eyal Taylor]

Hi @Bill Sheboy

Thank you for reaching out.

I am an Org Admin of our Cloud Jira and I am trying to create a scheduled automation as a one off to populate a custom field i created using JQL query to choose which tickets to apply the rule to. For now, my query returns one valid ticket for testing.

We would like this field to be populated with a date which should be a sum, calculated by the following:

Start Date Field + (Story Points Field + Tester Story Points Field - 1) / 2 

'Estimated Delivery Date' - Date Picker Field

Start Date Field - Date Picker Field

Story Points field - Numerical

Tester Story Points field - Numerical

e.g.

Start Date = 2024-11-25

Story Points = 3

Tester Story Points = 2

2024-11-25 + (3 + 2 - 1) / 2 = 2024-11-27

Then we would expect Estimated Delivery Date to be populated with: 2024-11-27.

This should also consider Business Days, rather than just days.

Audi log is attached earlier.

Thank you!

[Bill Sheboy]

Thank you for the additional information, as that confirms the expression used to set the date is incorrect.

Please see the linked documentation I provided to correctly perform the math and then increment the date value:

• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/
• https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

I recommend using the Create Variable action to first calculate the increment value as that will make logging it for testing / debugging easier: https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Create-variable

[Eyal Taylor]

{{issue.Start date.plusDays(issue.Story points.plus(issue.Tester Story Points.minus(1).divide(2)).round)}}