Changing Colours depending on selection and + or - value

[ryanfoster84]

HI,

i have a text box with the following formula:

=if([Selection Type]='Month / Month', num($(vVarianceMTDRev),'#.%'),

if([Selection Type]='Year / Year',num($(vVarianceRevYr/Yr),'#.%'), 

num($(vRevenueQTDVariance),'#.%')))

basically this formula will display a % based on a selection either Month, Quarter or Year.

the issue i am having is i want to the colour to change if it is a negative or positive number so green for positive and red for negative.

i have tried a number of things in the calculated colour expression but nothing i do works.

this is one thing i tried:

if($(vRevenueQTDVariance)< 0

Or

$(vVarianceMTDRev) <0

Or

$(vVarianceRevYr/Yr) <0,red(), green())

but it keep everything red

Any help would be greatly appreciated.

[jonathandienst]

Rather than using a colour expressions, use the settings in the Visual Cues tab to automatically colour positive and negative values.

[avinashelite · Accepted solution]

try like this :

if(sign(if([Selection Type]='Month / Month', num($(vVarianceMTDRev),'#.%'),

if([Selection Type]='Year / Year',num($(vVarianceRevYr/Yr),'#.%'),

num($(vRevenueQTDVariance),'#.%'))))=-1,red(),green())

[ryanfoster84]

thank you this works brilliantly

[Kushal_Chawda]

can you share the sample app?

[ryanfoster84]

no sadly not as its sensitive information but @avinash r gave me the correct solution