Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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.   

Tags (4)
10 Replies
qvelhoff
Valued Contributor

Re: how to change background color for null values in a table

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

Re: how to change background color for null values in a table

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

qvelhoff
Valued Contributor

Re: how to change background color for null values in a table

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.

Highlighted
MVP
MVP

Re: how to change background color for null values in a table

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

Re: how to change background color for null values in a table

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

qvelhoff
Valued Contributor

Re: how to change background color for null values in a table

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. 

konstantin
Contributor III

Re: how to change background color for null values in a table

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

custom sell.png

MVP
MVP

Re: how to change background color for null values in a table

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

Re: how to change background color for null values in a table

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.