topic SV:Null as Null not zero in QlikView

Null as Null not zero

Fri, 28 Aug 2009 21:39:49 GMT

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 ?

Null as Null not zero

Fri, 28 Aug 2009 21:52:14 GMT

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

SV:Null as Null not zero

Fri, 28 Aug 2009 22:26:09 GMT

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

SV:Null as Null not zero

johnw — Sat, 29 Aug 2009 02:57:21 GMT

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.

SV:Null as Null not zero

Sat, 29 Aug 2009 03:08:25 GMT

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 ?

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

))

SV:Null as Null not zero

johnw — Sat, 29 Aug 2009 03:25:35 GMT

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?

Null as Null not zero

Tue, 10 Nov 2009 05:01:52 GMT

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

Null as Null not zero

Tue, 10 Nov 2009 05:03:10 GMT

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