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

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,467,417
Community Members
 
Community Events
177
Community Groups

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

Edited

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

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

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

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.

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

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events