topic Re: Coloring the background cells in Pivot Table based on value in cell in QlikView

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

Mon, 17 Feb 2014 19:38:59 GMT

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

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

Nicole-Smith — Mon, 17 Feb 2014 19:45:41 GMT

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())

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

Anonymous — Mon, 17 Feb 2014 20:56:37 GMT

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

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

Tue, 18 Feb 2014 03:57:56 GMT

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

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

sanketkhunte — Tue, 18 Feb 2014 04:21:47 GMT

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

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

Tue, 18 Feb 2014 13:36:34 GMT

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?

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

Tue, 18 Feb 2014 13:42:01 GMT

Hi,

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

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

Tue, 18 Feb 2014 13:47:18 GMT

Fantastic!. Thanks again.

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

Nicole-Smith — Tue, 18 Feb 2014 13:57:06 GMT

I tend to use these two sites when choosing colors:

http://www.colorpicker.com/

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