topic Changing Colours depending on selection and + or - value in QlikView

Changing Colours depending on selection and + or - value

ryanfoster84 — Wed, 02 Sep 2015 12:18:46 GMT

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

$(vVarianceMTDRev) <0

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

but it keep everything red

Any help would be greatly appreciated.

jonathandienst — Wed, 02 Sep 2015 12:21:38 GMT

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

avinashelite — Wed, 02 Sep 2015 12:22:15 GMT

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 — Wed, 02 Sep 2015 12:24:52 GMT

thank you this works brilliantly

Kushal_Chawda — Wed, 02 Sep 2015 12:25:11 GMT

can you share the sample app?

ryanfoster84 — Wed, 02 Sep 2015 12:28:48 GMT

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