Complicated color definition

[#1]

Hi Everyone,

I have the following chart:

I have defined the colors using the folowing in background color:

=if(SOURCE_CATEGORY='Local', rgb(0,77,141)
 ,if(SOURCE_CATEGORY='National', RGB(141,0,77)
 ,IF(SOURCE_CATEGORY='Medical', RGB(77,141,0))))

This is working great. Here's the problem. This chart has a set analysis statement so that it acts independently of the field that defines these categories:

e.g.

{<category=>}

So when I select one of these categories in a list box (which I need to be able to do for other charts on the sheet) the colors of the non-selected pies changes! For example, if I select 'National', the National pie segment remains the correct color but the other two change to random colors.

Any ideas? Persistent colors is checked in the settings.

Thanks

Gareth

[#2]

Maybe i'm missing something but if you graph is just showing a breakdown between Local, Medical, and National and you select National, wouldn't the whole graph be only National and just one color?

Maybe you can post some code, or maybe i'm just missing something.

[johnw · Accepted solution]

moshea444 wrote:Maybe i'm missing something but if you graph is just showing a breakdown between Local, Medical, and National and you select National, wouldn't the whole graph be only National and just one color?

If I understand correctly, the graph is intended to ignore the selection of 'National' and continue displaying all three. The problem is that when 'National' is selected, the other values don't get the right color.

I was able to duplicate and work around the problem in v9 SR6. Try this for the color expression.

pick(match(only({1} SOURCE_CATEGORY)
,'Local' ,'National' ,'Medical')
,rgb(0,77,141),rgb(141,0,77),rgb(77,141,0))

I didn't duplicate the EXACT situation, so I could have a syntax or other error. But the idea is that you use only({1}...) to force the expression to recognize even unselected categories.

[#4]

Ah John thanks again for your help on these forums. This worked perfectly!