Using a vaiable to alter background colour

[aliyoung92]

HI

The below expression is used to calculate variance, I'm using a Pivot and don't want to actually display the expression.

Simply use it to provide a Rag status in the background colour of another expression.

My plan was to set the expression as a variable then do the IF statement in the Expression I want to do the RAG status in.

=IF(IsNull(GetCurrentSelections()),(count({<Score_2016={'1','2'}>}Score_2016) / count({$-1<Score_2016={'6'}>}Score_2016)) - [All Responses],(count({<Score_2016={'1','2'}>}Score_2016) / count({$-1<Score_2016={'6'}>}Score_2016)) - MaxString([Sector Score]))

I then name the Variable: vVar16

Then below in the Background Colour

$(IF(vVar16 < '-0.05',LightRed(),IF(vVar16 >= '0.00', LightGreen(),yellow())))

It doesn't work and I don't understand why not.

Any help would be really appreciated

Thanks

[vinieme12]

you don't need $ expansion for the whole thing just the variable

just use

=IF($(vVar16) < -0.05,LightRed(),IF($(vVar16) >= 0, LightGreen(),yellow()))

also Numbers don't need single quotes just string literals

[vinieme12]

use as below

=IF(IsNull(GetCurrentSelections()),(count({<Score_2016={1,2}>}Score_2016) / count({$-1<Score_2016={6}>}Score_2016)) - [All Responses],(count({<Score_2016={1,2}>}Score_2016) / count({$-1<Score_2016={6}>}Score_2016)) - MaxString([Sector Score]))