Null as Null not zero

[#1]

I am having issue with expressions that have sum() function. All nulls are automatically converting to zero when I use sum() function to create an expression.

The only dirty solution I can find is using if else statement.

For example

=

if (sum(Value)<>0,sum(Value))

Does any one know the good solution to my issue ?

[#2]

=if(sum(value)<>0,sum(value)) will not serve the purpose as it will avoid displyying real zero as well.

[#3]

You should be able to calculate the fields within Value that are not null(or empty) with one of the following.

If value is empty:

if((Value)<>'',sum(Value),Value)

If value is null:

if(not isnull(Value),sum(Value),Value)

Hope this helps!

//Jakob

[johnw]

So if ALL values being summed are null, you want the sum to be null instead of 0? This should work:

if(sum(len(value))>0,sum(value))

The len(value) should be 0 for null, and >0 for anything non-null. So if sum(len(value))>0, it means that there is at least one non-null value to be summed. If not, we return null.

[#5]

Thank you every body for the help. However non of the above mentioned formula seems working for me. I am trying to apply formula on pivot table. Data are being pulled using SQL and SQL has group by clause.

The following formula seems to be working for me at this time. However i really would like to avoid using if else formula in my expression. Is there any other way to avoid zero for nulls while using sum() function ?

if

(count([Value]),Sum([Value]

))

[johnw]

I guess I just don't understand the problem you're having.

What does your data set look like?

What does your chart look like?

What do you WANT your chart to look like instead?

[#7]

I am also having the same challenge where I would like the sum of a null value to return null and not zero.

I was able to get the following to do the trick:

if(len(value))>0,sum(value)

but now how can I do this same evaluation when using set analysis. Here is the set analysis I am currently using:

sum

({$<SPO_INDICATOR={Equal}>}Value)

But when this expression will return 0 when the Value is null.

How do I add an if statement to set analysis?

Thanks,

Crystal

[#8]

Because the font came out so small, i am reposting what I just sent:

I am also having the same challenge where I would like the sum of a null value to return null and not zero.

I was able to get the following to do the trick:

if(len(value))>0,sum(value)

but now how can I do this same evaluation when using set analysis. Here is the set analysis I am currently using:

sum

({$<SPO_INDICATOR={Equal}>}Value)

But when this expression will return 0 when the Value is null.

How do I add an if statement to set analysis?

Thanks,

Crystal