topic Re: Cell color in pivot based on Dimensions in QlikView

Cell color in pivot based on Dimensions

Wed, 21 Oct 2015 07:05:00 GMT

Hi,

I have created a pivot table with two dimensions dimension1 and dimension2

One aligned horizontally and second vertically.

Say the dimension1 contains value a,b,c and dimension2 contains value x,y,z as

Dimension1\Dimension2	x	y	z
a	ax	ay	az
b	bx	by	bz
c	cx	cy	cz

I want the background cell color to be fixed to some specific color and different for all the cells. So, Have written an expression in background color of expression.

The dimensions are static.

So based on selections, few cell value turns to zero and also the background color fades to white.

Is there any way to handle this?

hic can u suggest something?

Thanks,

Anjali Gupta

Re: Cell color in pivot based on Dimensions

phaneendra_kunc — Wed, 21 Oct 2015 07:13:58 GMT

Do this.

1. Go to Menu bar --> View --> click on Design Grid

2. right click on pivot table and click on custom format cell

3. On custom format cell window you will find Apply change to drop down. Select Current expression from this drop down.

4. And Change the background color.

5. Along with the above you have to do the background color expression.

Here trick is Background color expression will take precedence and for all cross tab nulls/zeros design grid color will take next.

Re: Cell color in pivot based on Dimensions

rwunderlich — Wed, 21 Oct 2015 07:26:07 GMT

For dimensions(s) that is are not selected (or not possible), you want the cells background to be white? You can use any aggregation function to test the "1 - $" set, which is the inverse of current selections.

if(count({1-$} dimension1)>0, white())

-Rob

Re: Cell color in pivot based on Dimensions

hic — Thu, 22 Oct 2015 17:12:05 GMT

I assume that you have some color expression where you use field references for dimension1 and dimension2 to determine the color. Then you may need to use Only({1} dimension1) and Only({1} dimension2) instead.

HIC

Re: Cell color in pivot based on Dimensions

Sat, 24 Oct 2015 13:43:01 GMT

Thanks Henric for the help.