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

how to change background color for null values in a table

Thu, 18 Sep 2014 14:25:26 GMT

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.

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

NickHoff — Thu, 18 Sep 2014 14:33:20 GMT

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

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

Thu, 18 Sep 2014 14:47:33 GMT

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

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

NickHoff — Thu, 18 Sep 2014 15:06:13 GMT

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.

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

jagan — Thu, 18 Sep 2014 15:12:41 GMT

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.

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

Thu, 18 Sep 2014 15:41:51 GMT

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

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

NickHoff — Thu, 18 Sep 2014 15:46:49 GMT

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.

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

Anonymous — Thu, 18 Sep 2014 18:23:37 GMT

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

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

jagan — Fri, 19 Sep 2014 01:17:06 GMT

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.

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

prajuds99 — Fri, 19 Sep 2014 07:37:01 GMT

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.

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

Tue, 31 Mar 2015 00:40:16 GMT

I know this is an old post. This solution worked for me.

Thanks Jagan