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

How to refer to JIRA JQL status values in expr formula for Jira structure

Alex Agn June 18, 2021

Dear all,

Not sure I assigned my question to the right topic, my apologies if I'm not in the right space.

My question:
I would like to create new column in my jira structure in order to follow up progress on stories which should go live/be released at the end of the month.

In the formula I would like to refer to the STATUS values such as Ready for ACC, Ready for PROD, etc. applied in my projects but it seems that the EXPR formula is not recognizing these STATUS values.


I tried out the formula below (I'm not at all an expert in EXPR nor SQL :) ) but all issues get "OVERDUE".

if(Requestedbydate < today() and Status!="Ready for ACC"; "{color:red}OVERDUE{color}";
Requestedbydate < today() and Status!="Ready for PROD"; "{color:red}OVERDUE{color}";
Requestedbydate < today() and Status!="Released to ACC"; "{color:red}OVERDUE{color}";
Requestedbydate < today() and Status!="Released to PROD"; "{color:red}OVERDUE{color}"
;Requestedbydate < today() and Status!="Resolved"; "{color:red}OVERDUE{color}";
Requestedbydate < today() and Status!="Closed"; "{color:red}OVERDUE{color}";


DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Ready for ACC"; "{color:orange}Due soon{color}";
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Ready for PROD"; "{color:orange}Due soon{color}";
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Released to ACC"; "{color:orange}Due soon{color}";
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Released to PROD"; "{color:orange}Due soon{color}";
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Resolved"; "{color:orange}Due soon{color}";
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Status!="Closed"; "{color:orange}Due soon{color}";

DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Ready for ACC"; "{color:green}Still >7days{color}";
DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Ready for PROD"; "{color:green}OK{color}";
DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Released to ACC"; "{color:green}OK{color}";
DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Released to PROD"; "{color:green}OK{color}";
DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Resolved"; "{color:green}OK{color}";
DAYS_BETWEEN(today(), Requestedbydate) >7 and Status!="Closed"; "{color:green}OK{color}";



Thanks in advance!

Kind regards,
Alex

3 answers

1 vote
Alex Agn June 20, 2021

Dear all,
I got an answer of the almworks service desk which brought me in the right direction, 
I copy it below, you never know someone else could use it.

The 'OVERDUE' value is returned because of the conditions' contradictions - if an issue's Requested by date field is less than today's date and it has pretty much any status, it will be overdue regardless of other conditions(one condition can be met at a time). Singling out a certain Status like status!=xxx means that issues in all other statuses match the condition and if there is a similar condition that singles out another Status, they annul each other in a way because issues with a Status that doesn't match to the first condition match to the second condition.

To get the result you need, the conditions should be more specific and not overlap. Instead of specifying each Status, you can try using the Status category if all mentioned Statuses are of the 'Done' category. In this case, you can significantly simplify the formula:

if(Requestedbydate < today() and Statuscategory!="Done"; "{color:red}OVERDUE{color}"; 
DAYS_BETWEEN(today(), Requestedbydate) <=3 and Statuscategory!="Done"; "{color:orange}Due soon{color}"; 
DAYS_BETWEEN(today(), Requestedbydate) >7 and Statuscategory!="Done"; "{color:green}OK{color}";
status="Ready for ACC"; "{color:green}OK :D{color}";
status="Ready for PROD"; "{color:green}OK :D{color}";
status="Released to ACC"; "{color:green}OK :D{color}";
status="Released to PROD"; "{color:green}OK :D{color}";
status="Resolved"; "{color:green}OK :D{color}";
status="Closed"; "{color:green}OK :D{color}";
"{color:blue}Needs Requestedbydate{color}")
0 votes
JimmyVanAU
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 19, 2021

Hi Alex,

In your formula you have:

if(Requestedbydate < today() and Status!="Ready for ACC"; "{color:red}OVERDUE{color}";

 

Firstly, all of the results are set to evaluate to say OVERDUE in red colour, so even if the formulas were right, the results should be updated for each condition.

Secondly, the != means not equal to. If you have two statements with != then one of them would always evaluate. It's likely you should be using = so that each status returns a different value.

If you update those, let us know how you go, and let us know what your new formula is if you can't get it to work.

Cheers, Jimmy

0 votes
marc -Collabello--Phase Locked-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 18, 2021

Sorry, can't help you with your question directly, but if a add "JQL" instead of "SQL" to your question, others might find the question easier.

Alex Agn June 18, 2021

thanks for your feedback, I modified it in the title.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events