how to change background color for null values in a table

[#1]

Hi friends,

I have a pivot table having some null values,I want to change color of columns,but it will change only for those cell which has value,does anyone has any idea to fix this.   

[NickHoff]

Expand the expression for the value you wish to calculate.  You'll see Background Color, select this and in the expression add IF(valueyouwishtocalculate = 0, RGB(0,0,0))  In order to get this one to work you'll have to do the following:

You can replace the missing cell with 0. Go to presentation Tab in Chart properties.

Uncheck 'Suppress Zero-values

Uncheck Missing

Check populate Missing Cells

Specify the Missing Symbol and Null Symbol as 0(As shown)

You could also use IF(ISNULL(Valueyouwishtocalculate)),000))  IsNull assigns -1 if true and 0 if false, so you have to change to work with your statement or ifstatement.

Obviously you'll have to change the 0,0,0 to the color of your choice, or for certain colors you could use Red()), Black()), ect..

[#3]

But I want to change the color column wise,you can see in my attachment, I am using a pivot table where I change the table into cross table,then how to change the color in that application for null values

[NickHoff]

This solution will work:

You can replace the missing cell with 0. Go to presentation Tab in Chart properties.

Uncheck 'Suppress Zero-values

Uncheck Missing

Check populate Missing Cells

Specify the Missing Symbol and Null Symbol as 0

This changes the columns where the expression resides, if you want to do it column specific then you can add in set analysis.  Add the IF(Statement = 0, RGB(#,#,#) to your background and it will work.  If you want to hide the 0 value then you'll have to set the font color where Sales = 0 to the same RGB.

[jagan]

Hi,

Try like this

Press Ctrl + G to enable grid and right click on null value and select Custom Format Cell and select the background color you required.

Regards,

Jagan.

[#6]

But then it will apply for all null value cell,is this not possible to make the color of null value same as the other value has in the column

[NickHoff]

If you just do the following:

• You can replace the missing cell with 0. Go to presentation Tab in Chart properties.

  Uncheck 'Suppress Zero-values

  Uncheck Missing

  Check populate Missing Cells

  Specify the Missing Symbol and Null Symbol as 0

  With the expression Sales, and it's current background expression, it will color all the cells the color of the other values in that column.  Your problem is going to be dealing with the 0 or what ever value you assign the missing symbol/null symbol, perhaps if you just did a space instead it might work.  However, if you leave it as the default Qlikview won't read the NULL. 

[Anonymous]

right click over the null value and select custom format cell, then select background color

[jagan]

Hi,

It is not possible like other values, it won't consider the null values, I think this solution works for you, since you want all null values one color.

Regards,

Jagan.

[prajuds99]

Hi ,

I was facing same issue and Nicks's solution worked in my case.

I just put a space in 'Null Symbol' under presentation tab and green background color is working for Null values as well.