Formula field including project target / due date

[Christian.Lange]

We are in the process of revamping our metrics to calculate the "priority score" of our ideas. In the past we have used a rating field called "Urgency" which impacted the score as well as other fields.

Unfortunately the urgency of an idea is pretty dynamic and it cannot be set only once. In the past we have set it once, but for example, the closer a deadline comes, the higher the urgency need to be. 

While our overhaul project two things came to my mind and I am wondering if there is a solution or how others deal with this. 

Idea 1: 
It is possible to add the project target (or any other due date/deadline) as input to the score formular. With the the score would be impacted directly by a given due date. 

Idea 2: 
Use an automation which runs regularly: Take the project target date into account as well as the estimated effort of the idea and update the urgency field based on how much effort is expected and when is the due date set?  

For both ideas I have no clue if this is possible and if so, how it can be tackled.

Any inputs are welcome :)

[Nick Haller · Accepted solution]

Hi @Christian.Lange ,

JPD date fields are not currently supported in custom formulas. There is a feature request already open with engineering that I'll add this Community Question to as a +1.

Having said that, you can configure an automation that calculates the date differential between (for example) the Project target and current date. Then schedule this to run daily, or execute with some other component like if a certain field is updated:

1) WHEN: Scheduled daily (or whenever)

• JQL filters on my JPD project, and ideas that have a Project target value:
  • project = JPD AND cf[12345] IS NOT EMPTY
    • ^JQL can be whatever (this is simply an example), but it is recommended to add one to reduce the number of ideas the automation executes on, and chance of hitting automation limits

2) THEN: Create variable

• Variable name = varJiraDate
• Smart value = {{now.jiraDate}}

3) THEN: Create variable

• Variable name = ptarget
• Smart value = {{triggerIssue.customfield_12345.substringBetween("end\":\"","\"")}}

4) THEN: Edit issue field (edit JPD number field)

• {{varJiraDate.toDate.diff(ptarget.toDate).days}}

^ A few things to note:

• How to find any custom field's IDs
  • You will want to replace "12345" above with your Project target field's ID
• The variable names can be anything, but they will need to be used/match in step 4
• Step 3 uses string processing to extract the Project target date into a yyyy-mm-dd format
  • JPD date fields are actually formatted in a range, and displayed as a string
• Step 4 calculates the date differential (in days) between the Jira date and the extracted Project target date
• Automation smart values - date and time

And when this runs, it will show something like this on your ideas within the number field:

You could then inject this JPD number field into a custom formula. Hope that helps get you started! Let me know if there are any questions.

[Bill Sheboy]

Hi @Nick Haller 

Your created variable has a name of jiraDate, which is also the name of a function for date formatting in automation rules.

To avoid people and rules having confusion, I recommend always prefixing names in Create Variable, Create Lookup Table, and Advanced Branches with some indicator text.  For example, using varJiraDate rather than jiraDate.

And, your link to the "string processing" is for JSM, and not the functions used for Jira Automation in JPD and software projects.  Here is the correct link: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-text-fields/

Kind regards,
Bill

[Nick Haller]

Hi @Bill Sheboy ,

Good suggestions! I've made the edits / adjustment in my answer above, in both the screenshot and body.

As for the string processing doc., I'm aware it's tailored towards JSM, but the formatting and methods should still work within automations.

Regardless, I swapped the hyperlink in my answer to reflect the one you shared. I agree it's better. Thank you!

[joachim ramberg]

Hi,

If I understand you correctly, you want to use "days left until deadline" as input in your priority score formula.

I would experiment with the following:
Create a custom field of type number, find the custom field id through this page (https://[your-subdomain].atlassian.net/rest/api/3/field). This field will be updated once every day by an automation. I think you would be able to calculate days left until deadline with smart value {{compareTo(date)}} (https://confluence.atlassian.com/automation/jira-smart-values-date-and-time-993924864.html).

The automation would edit the issue with something like below.

{
    "fields": {
        "customfield_00000":"{\"start\":\"..........\",\"end\":\"......\"}"
    }
}

[Christian.Lange]

Thanks for your input. based on your inputs I was able to take a huge step forward. Due to the complexity, the automation is not that easy. Needs some more testing but not on a Friday evening ;) 

[Christian.Lange]

thanks a lot for all of your inputs!

In the end I went with the following for now: 

To get the number of days until the "end" date of the project target value, I put this in a var:

{{now.diff(triggerIssue.customfield_10129.substringAfter("\"end\":\"").substringBefore("\"").toDate("yyyy-MM-dd")).days}}

Because I wanted to take the effort into account, I created a another variable taking the rated effort from the issue itself: 

{{triggerIssue.customfield_10115}}

Then the if/then/else (oh how easy a switch/case would be ;) ) orgy started. Not posting everything, but in theory it was something like: 

IF daysUntilDeadline < 15 THEN set Urgency to 5

IF daysUntilDeadline < 30 THEN

    IF ideaEffort == 0 THEN set Urgency to 4
    ELSE set Urgency to 5

IF daysUntilDeadline < 60 THEN

    IF ideaEffort == 0 THEN set Urgency to 3
    IF ideaEffort == 1 THEN set Urgency to 4
    ELSE set Urgency to 5

IF daysUntilDeadline < 90 THEN

    IF ideaEffort == 0 THEN set Urgency to 2
    IF ideaEffort == 1 THEN set Urgency to 3
    IF ideaEffort == 2 THEN set Urgency to 2
    ELSE set Urgency to 5

... and so on. 

Long story short: with a lots of If/then/else and the variables "effort" and "daysUntilDeadline" (which was populated from the project target field) I was able to achieve what I wanted. 

Thanks again!

[Nick Haller]

Glad to hear @Christian.Lange ! Thank you for sharing all those details as well. I'm sure others within the JPD Community will appreciate them.