Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to change background color for null values in a table

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.   

10 Replies
NickHoff
Specialist
Specialist

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..

Not applicable
Author

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
Specialist
Specialist

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
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Specialist
Specialist

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
Not applicable
Author

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

custom sell.png

jagan
Luminary Alumni
Luminary Alumni

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
Contributor II
Contributor II

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.