Background colour set in expression

[#1]

Hi Guys

I have a pivot table and I wish to set conditional background colours , but I need to set it on multiple selections,

Basically if 

                  Field [Report] = CBIRE AND [Total] < 70 go Green

                  Field [Report] = CBIRE AND [Total] < 110 go Orange

                  Field [Report] = CBIRE AND [Total] > 70 go Red

AND

                  Field [Report] = ROI AND [Total] < 250 go Green

                  Field [Report] = ROI AND [Total] < 500 go Orange

                  Field [Report] = ROI AND [Total] > 500 go Red

I actually have another 4 rules based on different options in Report and Total to add in. But I have tried the first part using

if(Report='CBINT' and Total <=70,RGB (0,255,0),if(Report='CBINT' and Total<=110,RGB (255,255,0),RGB (255,0,0)))

but this is turning them all red regardless of what is filtered or not.

Can anyone please help ?

Thanks

A

[stigchel]

It also might be a good idea to load the conditions/limits in fields linked to the report field, say 'Lower' and 'Upper'. You could then have a variable checking which report is selected, or none, and set the values.

i.e. Value for vLower=if(GetSelectedCount(Report)=0,vLower=920,vLower=Lower)

Expression for color:

if([Total]<=vLower,RGB (0,255,0),

if([Total]<=vUpper,RGB (255,255,0),

if([Total]>vUpper,RGB (185,0,0))))

[#3]

Hi Stigchel,

I have tried your suggestion , so what I wish to happen is that when no selection is Report the conditions <920 Green , <1406 Orange and >1406 Red. But now no condtional formating works at all, I know the first part of the formula works , as I've checked it . its just getting the condition when no Reports are selected from the drop down ( to be honest in some ways its actaully when all reports are selected in the data tho !!! perhaps this is the issue with the getselected fields !!

Can you help ?

if(Report='CM Banking INT' and [Total]<=600,RGB (0,127,0),if(Report='CM Banking INT' and [Total]<=800,RGB (255,128,0),if(Report='CM Banking INT' and [Total]>800,RGB (255,0,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,127,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,128,0),if(Report='PAYMENTS/I&I' and [Total]> 110,RGB (255,0,0),if(Report='IRE' and [Total]<=50,RGB (0,127,0),if(Report='IRE' and [Total]<=75,RGB (255,128,0),if(Report='IRE' and [Total]> 75,RGB (255,0,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=250,RGB (0,127,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=500,RGB (255,128,0),if(Report='RAIB Bank/AIB(UK)' and [Total]> 500,RGB (255,0,0),if(GetSelectedCount(Report)=0 and[Total]<=920,RGB (0,127,0),if(GetSelectedCount(Report)=0 and [Total]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

[#4]

Hi Stigchel

I have used your rational and in ths script put the following

if([Total]<70,'Green',if([Total]>70 and [Total]<110,'Orange','Red')) as [RAG Code],

so now in my condtional expression I can write it as

if([RAG Code]='Green',RGB (0,127,0),if([RAG Code]='Orange', RGB (255,128,0),RGB(255,0,0)))

Which again works great so long as I have a Report selected, can you help me add a condition that states when Reports is not specified ?

Thanks

[stigchel]

There is a space missing between "and[Total]" in your previous to last post. Add a space and the expression will work for any single or none selection in report.

[stigchel]

This still leaves you with the problem of multiple selections in report. You might want to control this by allowing only one selection (e.g. the option "always one selected value"). If you need the total as selection, load a crosstable with the report values and a total.

[#7]

Hi Stigchel

The [Total] column is actually got some crazy name so for the sake of easy reading on the forum I have being pasting total across the name Below is the actual formula , I think there actually is a space there so I am double stumped !!!

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0),

if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0), i

f(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0),

if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0),

if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0),

if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0),

if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0),

if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

[#8]

Hi Stigchel,

Due to this specific data there will only ever be one report selected , so I think if I can write the condition that says when no [RAG Code] follow condition < 920 Green , >1406 Orang < 1406 Red,

Going to try that now

[stigchel]

In the above expression one "if " is incorrectly broken off (line 8). I have corrected and used this expression succesfully in a document of mine (offcourse replacing your fields with ones that exists in mine)

[#10]

Is the condition working when no fields are selected?

Mine are all just Red

( the above was just a paste error into this forum )

[stigchel]

You can also load the values for R and G and B in seperate fields instead of a textual colour coding and in the color expression use:

=RGB(R,G,B)

If you have named these fields "R", "G" and "B"