Put the value of a variable in red if negative, green if positive

[Nadya_BA]

Hi all,

I've this formula. I would like to change it in order to put the value of DeltaSP in red if negative and in green if positive.

Please pay attention that I'm applying also the option "sum over sub-items"

Can you help please? Thank you

[David Niro · Accepted solution]

Hello @Nadya_BA ,

As Sam mentioned, yo do want to change the format to Wiki Markup first.  We will need to also use IF conditional statements to define what should happen if the DETLASP value is greater or less than 0.

This sample formula will return the value at the level you have your Story Points entered:

WITH DETLASP = 
  Storypoints-actualSP
:

WITH DSP_CONDITION =
  IF actualSP > 0 AND DETLASP !=0:
    DETLASP
:

IF (DETLASP > 0;
  CONCAT(
    "{color:green}",DETLASP,"{color}"
  );
DETLASP < 0;
  CONCAT(
    "{color:red}",DETLASP,"{color}"
  )
)

However, like your other post, sum over sub-items will not work here.  You will want to create additional conditional statements for the levels you plan to Aggregate.  You probably also want to save the Green and Red color formats as local variables.

Please let me know if this helps!

Best,
David

[Nadya_BA]

@David Niro 

I tried this formula, it returns correct value & coloring for stories but it returns always 0 for epics and features..

[David Niro]

Hi @Nadya_BA ,

It will need some modifications based on the hierarchy and what value you want to roll up. 

Is it the same Feature > Epic > Story hierarchy from before?

Are you trying to just get a sum at the Epic and Feature levels (2+5+-3 = 4)?, or some notification that something is in the negative (2,5,-3 = -3)?

Best,
David

[Nadya_BA]

@David Niro Indeed, I'm trying to sum the differences of (expected story points - actual story points)  in Epic and feature level.

I'm not sure if we can sum differences between numbers .. but, yeah this is what I'm trying to do.

[David Niro]

Hello @Nadya_BA ,

Could you please give the formula below a try?

WITH FORMAT_COLOR(color,value) = 
"""{color:$color}$value{color}"""
:

WITH EPIC_CALC = IF(
issuetype = "Epic"; SUM#children{
IF(actualSP > 0 AND (storypoints - actualSP) !=0;
storypoints - actualSP
)}
):

WITH DETLASP = 
Storypoints-actualSP
:

WITH DSP_CONDITION =
IF(issuetype = "Story" AND actualSP > 0 AND DETLASP !=0;
DETLASP
)
:

WITH FEATURE_CALC = IF(
issuetype = "Feature"; SUM#leaves{
IF(actualSP > 0 AND (storypoints - actualSP) !=0;
storypoints - actualSP
)}
):


IF(DSP_CONDITION > 0;
FORMAT_COLOR("green",DSP_CONDITION);
DSP_CONDITION < 0;
FORMAT_COLOR("red",DSP_CONDITION);
EPIC_CALC > 0;
FORMAT_COLOR("green",EPIC_CALC);
EPIC_CALC < 0;
FORMAT_COLOR("red",EPIC_CALC);
FEATURE_CALC > 0;
FORMAT_COLOR("green",FEATURE_CALC);
FEATURE_CALC < 0;
FORMAT_COLOR("red",FEATURE_CALC) 
)

Looking forward to your feedback.

Best,
David

[Nadya_BA]

Thank you @David Niro . This worked!

[Sam Levite]

Please try changing the output from "General" to "Wiki markup" and create a color or icon for the red ones and the green ones!

https://wiki.almworks.com/documentation/structure/latest/data-center-and-server/wiki-markup-in-formula-columns-57940075.html

[Alexandria _Lexi_ Codd]

I'm hopping someone can help me with my even simpler formula, I want the same thing. I want the number to be red if it's negative and green if it's positive, but I'm not using "with" and I can't figure out how to get the text to change. I can do it in my other formula columns which use if statements, but any combination I try adding here isn't working