Formula column to change color of text based on last response time.

Question

Hi all,

I am looking to create an SLA reporter that formats the last comment column in JIRA structure view based on the current date and the last comment that was made.

So far. I can create a last comment view using the following code from that ALM works examples.

comments.UMAX_BY($.created).map(CONCAT( FORMAT_DATETIME($.created, "dd/MMM/YY"), " : ", $.author.user_display_name(), " - ", $.body))

Outputs this format.

<date> : @User - comments

I would like to modify the code above, and have tried to do this with "IF | CONCAT" statements interwoven into the code above, but all I seem to get are errors.

I would like to format the color of the date such that if the DAYS_BETWEEN(today(), $.created) >= 30, CONCAT("{color:darkorange}",FORMAT_DATETIME($.created, "dd/MMM/YY")Below is one of my attempts. comments.UMAX_BY($.created).map(if(DAYS_BETWEEN(today(), $.created) >= 30, CONCAT("{color:red}",FORMAT_DATETIME($.created, "dd/MMM/YY"),"{color}",
DAYS_BETWEEN(today(), $.created) >= 14, CONCAT("{color:darkorange}",FORMAT_DATETIME($.created, "dd/MMM/YY"),"{color}",
CONCAT(FORMAT_DATETIME($.created, "dd/MMM/YY"))))))

Any help would be much appreciated!

David Niro · Answer

Great catch @SCic !

@pritchsc  , I would also make some additional adjustments to the formula.

today() can be very heavy as can DAYS_BETWEEN(), and since we are using them more than once, I would store them as local variables so they are calculated once and then used throughout the rest of the formula.

Could you please try the formula below and let me know if it helps?

with _comments = comments.UMAX_BY($.created).created:
with _formatComments = _comments.FORMAT_DATETIME("dd/MMM/YY"):
with _today = today():
with _calculation = DAYS_BETWEEN(_comments,_today):

IF _calculation >= 30:
    CONCAT("{color:red}",_formatComments,"{color}")
ELSE IF _calculation >= 14:
    CONCAT("{color:red}",_formatComments,"{color}")
ELSE:
    _formatComments

SCic · Answer

Hi,

I believe there was a problem with the CONCATs closing brackets.

Try the following:

comments.UMAX_BY($.created).map(if(DAYS_BETWEEN(today(), $.created) >= 30, 
CONCAT("{color:red}",FORMAT_DATETIME($.created, "dd/MMM/YY"),"{color}"),
DAYS_BETWEEN(today(), $.created) >= 14, 
CONCAT("{color:darkorange}",FORMAT_DATETIME($.created, "dd/MMM/YY"),"{color}"),
FORMAT_DATETIME($.created, "dd/MMM/YY")))

Thank You,

Shay