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: 
Anonymous
Not applicable

Background Color Expression with NULL Values

Hi,

I would like to have the background color for a pivot table that has NULL values in a calculated dimension field     

[

if((Sum(SalesAmt)/Sum(Total<[Region],[SalesRepName]>SalesAmt)) > 0, Sum(SalesAmt)/Sum(Total<[Region],[SalesRepName]>SalesAmt), 0)

]

Here is what I was thinking:

Colormix1(if((Sum(SalesAmt)/Sum(Total<[Region],[SalesRepName]>SalesAmt)) = 0,0), red() , white())

I am seeing "-" as opposed to 0, and I think this is causing my colormix function to fail.

6 Replies
sunny_talwar

In that case may be this:

Colormix1(if(Len(Trim((Sum(SalesAmt)/Sum(Total<[Region],[SalesRepName]>SalesAmt)))) = 0, 0), red() , white())

Anonymous
Not applicable
Author

Hi Sunny,

That did not work

Screen Shot 2015-10-15 at 3.40.41 PM.png

robert_mika
Master III
Master III

Are there NULL or missing values?

BTW. NULL is not 0 -  is a unknown value.

Here is a topic on this

Missing values in PivotTable

and so far there is not much you can do.

(You can suppress missing value in QV)

Anonymous
Not applicable
Author

Thanks Robert

robert_mika
Master III
Master III

See my post above..

ManuelLindekeit
Contributor III
Contributor III

I just came across the same issue and it can be handled using visual cues background settings rather than in the expression. In my case I defined upper as >=0 (red) and made all other background settings green. Result is that nulls are green.