11 Replies Latest reply: Jun 24, 2015 1:28 PM by Simon Hogg

# Null condition in color expression

Hi, I have two measures, Test and Compare. I want to change the color of my chart based on whether Test>Compare or not. However there are some null values in Test.

My expression if(sum(Test)>sum(Compare),red(),green()) does not work because of the null values in Test. Is there a way to add the null condition into my if statement?

Thanks.

• ###### Re: Null condition in color expression

You may be able to something like this?

If(Len(Trim(Sum(Test))) = 0, Blue(), If(Sum(Test) > Sum(Compare), Red(), Green())

Best,

Sunny

• ###### Re: Null condition in color expression

Hi Sunny I tried this but it did not work...

• ###### Re: Null condition in color expression

Can you share a sample or show screenshots?

• ###### Re: Null condition in color expression

what is dimension and expressions in your chart?

• ###### Re: Null condition in color expression

Test and Compare are both measures in my chart. Name is the dimension.

• ###### Re: Null condition in color expression

You mean to say...

Dimension

Name

Expression

SUM or COUNT or some function (Test)

and SUM(Compare)

You want to change color of both chart?

kindly clarify on this!

• ###### Re: Null condition in color expression

Hi Manish, it is just one chart. Sum(Test) and Sum(Compare) are measures of the chart and Name is the dimension. It is a scatterplot. I want to change the color that when Sum(Test)>Sum(Compare), show me red, and otherwise green. There are null values in Test that is messing up the condition. Thanks.

• ###### Re: Null condition in color expression

nulls should be treated as zero by sum(). What happens if you put sum(Test) in a text object?

Rob

• ###### Re: Null condition in color expression

You'd think so, wouldn't you.  Unfortunately it doesn't seem to work for me (although I suspect this might be dependent on data-source).

In my data-load script I specifically have to test for NULL values and replace them with zeroes;

IF(ISNULL("[IPH29NKH]") , 0 , "[IPH29NKH]")) as "Invoice Value"

• ###### Re: Null condition in color expression

Use the Visual Cues option in the Chart.