Automation - determine fiscal quarter and year of duedate

[Jack Brickey]

I am hoping that someone has a more elegant means of achieving my goal.

Goal

Using automation I want to select the fiscal year and quarter from a drop-down custom field based on the due date.

Current verbose solution

I have created a test rule that uses an If/else condition that brackets each quarter of a fiscal year which in my case is July to June. I have provided an example snippet below. In my opinion there are two issues with this one is it is quite verbose and the second is that it will have to be manually updated every year.

thanks!

[Bill Sheboy · Accepted solution]

Hi @Jack Brickey 

UPDATED, to simplify quite a bit :^)

You could try this:

• knowing when your fiscal year starts, increment the date by 6 months to align
• use the format() function to get the quarter, using the year-of-era "y" and quarter "Q" formatting

For example, assuming the fiscal runs from July to June and you want the value for the Due Date:

The duedate of {{issue.duedate.jiraDate}} is in *{{issue.duedate.plusMonths(6).format("yy")}} Q{{issue.duedate.plusMonths(6).format("Q")}}*

You could test / adjust this by first creating a variable with a list of date values, and then using an advanced branch to iterate over the values to confirm they work as you wish.

Please note well: the "y" format is key for this scenario as that is the year-of-era rather than the "Y" week-based-year (which would off by + / - 1 year at the boundaries of weeks in January and December.

Kind regards,
Bill

[Jack Brickey]

This looks quite awesome @Bill Sheboy ! I will play with this tomorrow. Thanks!

[Jack Brickey]

@Bill Sheboy , this was a huge help for me and with a slight adjustment it works beautifully! I plan to create an article on this to the benefit of the Community.

For my scenario the FY is July-June, for example - July 2024 is in FY25 Q1 and Jan 2025 is in FY25 Q3. So here is what worked for me.

Details of the smartvalues

The duedate of {{issue.duedate.mediumDate}} is in *
FY{{issue.duedate.plusMonths(6).format("yy")}}
Q{{issue.duedate.minusMonths(6).format("Q")}}*

Rule screenshot

[Jack Brickey]

oops! i replied on my Atlassian Team account :-) 

[Bill Sheboy]

Thanks, and...

If the fiscal year is a months-shift why wouldn't both adjustments be plusMonths() or minusMonths() consistently, using the one which is correct for the Year value?  (Although it should not matter for the Q number with a 6-month shift.)

[Jack Brickey]

Ha, well of course you are correct. It is counter intuitive that plus or minus both work for the quarter, but, technically using plus here would be the right method. 

[Bill Sheboy]

Jack, I updated my first post to match your findings...just in case people stop reading too quickly and get puzzled by the results.  Thanks, again!

[Jack Brickey]

@Bill Sheboy , I am now taking my problem/solution to the next level and hoping you can Guide me further. My goal is to be able to filter/sort issues based upon fiscal year and fiscal quarter independently. 

I have created two custom select fields: Fiscal year (FY24, FY25, FY26...), Fiscal quarter (Q1, Q2, Q3, Q4). I then extended my automation rule as below.

the values depicted in my log action are as expected (FY24 Q1, as an example) but the log indicates errors on the edit of the two fields...

I suspect that my expectations of using the variables to set the desired value in the select list is misguided. I'm going to dig into the Community to see if I can find more on this approach but thought I would continue here.

[Bill Sheboy]

Hi, @Jack Brickey 

Without seeing the specifics of your rule's Edit Issue action, that seems to indicate the custom fields are not available for the issue.

First, I would use an example issue to confirm they are supported, and the exact smart value / custom field ID for the fields: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

Next, I expect you may need to use JSON to set the value of the fields rather than selecting them from the dropdown list: https://support.atlassian.com/cloud-automation/docs/advanced-field-editing-using-json/#Single-select-custom-field

For example, with made-up ids:

{
  "fields": {
    "customfield_12345" : { "value": "{{fiscalYear}}" }, 
    "customfield_67890" : { "value": "{{fiscalQuarter}}" }
  }
}

If that does not work, confirm the values exactly match the dropdown list for the custom fields (e.g., confirming there are no stray spaces to trim off).

[Jack Brickey]

Again much appreciated Bill!

In fact, based on another post where you referenced advanced-field-editing-using-json  document, I was able to determine that indeed needed the advanced JSON approach. And in fact I was just implementing and testing it successfully. As always I greatly appreciate your input but do apologize for prematurely asking for your support. :-(

{
    "fields": {
        "customfield_84886": {"value": "{{fiscalYear}}"}, "customfield_84830": {"value": "{{fiscalQuarter}}"}
    }
}

[Bill Sheboy]

Well done, and I am glad to help!

[Aaron Geister]

You could try to use the startOfyear(3) function to break this into Quarters. I have used this before, so they don't need manual updating.

[Jack Brickey]

thanks @Aaron Geister , so using JQL instead of smartvalues? note startofyear(3) = 3 years in the future, right? maybe you mean like

Q1 == duedate >= startofyear() and duedate < startofyear(4M)

and then have similar else legs for other quarters? Of course there is still the fiscal year part. So lets say I want to express as FY25 Q3 then maybe I can extract the year and offset to align with my offset FY (July 25 - Jun 26 = FY25).

[Aaron Geister]

That is what I meant. This is what I have had to do in past to limit from making more fields. I do believe after looking at Bills answer I would use that as it seems pretty good. But JQL would be able to do this as long as you could define those dates without using dates you need to update.