Coloring the background cells in Pivot Table based on value in cell

[#1]

Hi,

I have dimensions listed s RegionLevel1, RegionLevel2, RegionLevel3 and "TestCases" in Dimensions.

In expressions, I have TestStatus which has values/data as "Open","Fixed","Closed" and "Reopen".

I have created the Pivot table and data is appearing as I expected.

Now I want to clour the data cells with different colors-"Red","Yellow","Green","Blue".

Where should I do it and How do I do it?

I have looked at Visual Cues but it needs numeric values where as I have text values in my data.

Please could you help me in setting background color for data cells based on values?

Thanks

Prasad

[Nicole-Smith · Accepted solution]

There should be a plus button to the left of your TestStatus expression.  Click that plus, then in Background Color you can write an expression to color your stuff, such as:

pick(match(TestStatus, 'Open', 'Fixed', 'Closed', 'Reopen'), red(), yellow(), green(), blue())

[Anonymous]

Hi prasad,

Try below code:

In Background of an expression

If ( Status is = "Open",lightred())

If ( Status is = "Fixed",lightcyan())

If ( Status is = "Closed" ,lightgreen())

If ( Status is = "Reopen",lightblue())

Or try with nested if's

[#4]

Hi Prasad,

Expression--> +--->maje it - then--->Bgcolor---then in the def write like this

Use Pick(match(fieldname,'Open','Closed','Fixed'),rgb(255,255,255), rgb(110,210,120),Green()).

-------Use like. It  gives performance wise better than nested if.

i hope helps you.

Regards

Mahesh T

[sanketkhunte]

Hi Prasad,

Use following highlighted Background Color property to write an expression. You can write expression over there and you may also use RGB() function to exact color code match.

Thanks

Sanket

[#6]

Thanks all very much for your quick responses.

Now I know where to make the change  "Expression > + > Background Color" and what to write in the "Definition" - Pick(Match...) & If options.

I tried both and it worked.

I also liked the 'light' colors & rgb options.

It would be nice to have a  readily available list of colors/ rgb combinations and how they look. Do you have any?

[#7]

Hi,

go for caption tab and u can get diffrent colors and rgb values find the attachemnet.

[#8]

Fantastic!. Thanks again.

[Nicole-Smith]

I tend to use these two sites when choosing colors:

http://www.colorpicker.com/

http://tools.medialab.sciences-po.fr/iwanthue/index.php