background colour to change depending on expression

[rcorcoran]

Hi I have a heat map where I need different background colours depending on the % of target the expression is.  I am using the background colour in the expression window so see below I am trying to get if the sum of Physio expression below is above target column total it will be green etc. I can't get it to work for this expression alone and I also need to add on the next expression which is below but will have different colours ie red if over target etc.

Need to get this working:

=if([National]=' ' or [National] ='Data gap',RGB(191,191,191),
if(Sum({$<Physio={28,29,30,31}>}ActualValue)/Sum({$<Physio={28,29,30,31,32,33,34,35}>}ActualValue)>=[Target 95%],RGB(81,173,152),
if(Sum({$<Physio={28,29,30,31}>}ActualValue)/Sum({$<Physio={28,29,30,31,32,33,34,35}>}ActualValue)<=[Target 85%],RGB(208,62,81),
if(Sum({$<Physio={28,29,30,31}>}ActualValue)/Sum({$<Physio={28,29,30,31,32,33,34,35}>}ActualValue)>[Target 85%] and if(Sum({$<Physio={28,29,30,31}>}ActualValue)/Sum({$<Physio={28,29,30,31,32,33,34,35}>}ActualValue)<[Target 95%],RGB(233,174,17))))

then need to add this on so can change to different colours

Sum({$<OccTherapy={92,93,94,95}>}ActualValue))))/((Sum({$<OccTherapy={92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111}>}ActualValue)))

sorry I know I am probably not making sense but I can't give example as sensitive data loaded in table.

[a_mullick]

Hi,

With regards to the first expression, the 2 red brackets near the start suggest that you have not got a matching set of opening / closing brackets.

I've replaced your expression with a reference to the column() which actually contains the expression for the % calculation - which I reccommend you do too, as it makes it easier to see what's going on with complex conditionals like this. If it's the second expression then change it Column(2) etc

if([National]=' ' or [National] = 'Data gap', RGB(191,191,191),

    if(Column(1)>=[Target 95%],RGB(81,173,152),

        if(Column(1)<=[Target 85%],RGB(208,62,81),

            if(Column(1)>[Target 85%] and Column(1)<[Target 95%],RGB(233,174,17)))))

Hope that helps,

Azam

[a_mullick]

Just in case that wasn't clear, you can refer to expressions by their column number:

[rcorcoran]

That’s great Azam I didn’t know you could do this with expressions – makes it easier.

Rhona

[rcorcoran]

But my issue is that within each expression I have a number of expressions that I need to colour code So within Column (1) I have 8 expressions with different colour codes

Rhona

[a_mullick]

Sorry...not sure what you mean. Is there any chance you can provide an example in Excel just to make it clearer what you want the behaviour to be...don't need to see the data but just some example numbers.

Thanks,

Azam