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

Changing the color of cell when specific value returned

I have the Missing Symbol setup to display the value "N/A" and I want to make the cell change color when this value is returned. Is the reason why it won't change the color of the cell when the value is "N/A" because it represents a field that has returned no data? I am able to change the cell color when a number is returned as a value. I have this setup in an Expression under the Background Color Expression.

thanks,

Ryan

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

Again, "The actual value is very likely null(). One option might be to set the color when you have null()."

Here's the example modified to set the color when you have null(), and to use N/A as the null symbol. I tried to figure out how to get missing data to test that as well, but all I could get was null data. I use len(Value)<1 rather than isnull(Value) because isnull() doesn't work in some environments (64 bit server? I keep forgetting.).

View solution in original post

13 Replies
johnw
Champion III
Champion III

I don't know for sure, but I'd guess you're right. "N/A" is almost certainly not the actual value in the field, it's just what's displayed in the field. The actual value is very likly null(). One option might be to set the color when you have null(). Another might be to change the expression to actually return "N/A" when the value would have been null.

manishkumar75
Partner - Creator II
Partner - Creator II

Hi Ryan,

As per my understanding, you want to change the Cell Color for which the value is "N/A". I have attached an example to achieve this.

Hope it will work.

- Manish

Anonymous
Not applicable
Author

I think you might be looking to make the null cell a diffent color. I modified Manish code to deal with negative and positve values. If you use the background Color option under the desired Expression you will get the color change your looking for. I didn't work out the dispay of the N/A as you stated you had the working.

Here is the Background Color definition I used in example:

=



If(Isnull(Value),blue())

Thanks,

JS

Not applicable
Author

Thank you, John. I still couldn't get it to work properly and I think it's because it won't read "Missing Symbol" I appreciate your help though!

-Ryan

Not applicable
Author

Hi Manish,

thank you. I still couldn't get it to work properly and I think it's because it won't read "Missing Symbol" which I entered as "N/A". I appreciate your help though!

-Ryan

johnw
Champion III
Champion III

Again, "The actual value is very likely null(). One option might be to set the color when you have null()."

Here's the example modified to set the color when you have null(), and to use N/A as the null symbol. I tried to figure out how to get missing data to test that as well, but all I could get was null data. I use len(Value)<1 rather than isnull(Value) because isnull() doesn't work in some environments (64 bit server? I keep forgetting.).

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

John,

Good reminder about IsNull() differences in 32bit and 64bit. I've added a warning on this issue to the "Mistakes you can make" wiki page
http://community.qlik.com/wikis/qlikview-wiki/mistakes-you-can-make.aspx

-Rob

Not applicable
Author

John,

thank you for figuring out my issue. this worked perfectly and will help others in my company.

-Ryan

Not applicable
Author

Can something like this be done with charts based on the values? For example, make this bar which exceeds 100, then the color is red, if under 100 but greater than 50 yellow, under 50 then green.

Thanks.