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

Question

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

Alex Agn · Answer

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}")

JimmyVanAU · Answer

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

Alex Agn · Answer

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