Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean: Not applicable

## Problem with Null Values

Hi Guys,

I have a scenario, my expression is: count(A)+count(B).

If count(A) is null, the expression returns null even if count(B) has a value.

By right, I want the expression to return the value of count(B) whenever count(A) is null.

Can anybody suggest a solution to this problem?

Thanks

1 Solution

Accepted Solutions  Partner - Champion III

Hi

To avoid calculating the counts more than once, you have two options

1. Use the Alt() function

=Alt(Count(A), 0) + Alt(Count(B), 0)

2.Use the Rangesum function

=RangeSum(Count(A), Count(B))

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies  Master II

try this

count(A+B)

hope it helps

see the attached file..  Partner - Specialist

HI,

Try this

If ([count(A)]= Null(),[count(B)],IF([count(A)]= Null(),[count(B)], [count(A)] +[count(B)]))

Regards,

Nirav Bhimani  Master III

hi,

no such issue with mee..

well try this expression as

if(isnull(count(A)<>0,(Count(a) + count(B)),Count(B))

Hope it goes well..

111111  Partner - Champion III

Hi

To avoid calculating the counts more than once, you have two options

1. Use the Alt() function

=Alt(Count(A), 0) + Alt(Count(B), 0)

2.Use the Rangesum function

=RangeSum(Count(A), Count(B))

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein  Master

Hi,

QlikView have a lot of functions for developer. NumSum( expr1 [ , expr2, ... exprN ]) ==> Returns the numeric sum of 1 to N arguments. As opposed to the + operator, numsum will treat all non-numeric values as 0.

Ex:

NumSum(1,2,4) returns 7

NumSum(1,'xyz') returns 1

NumSum(Null()) returns 0

So within your case, you can try: NumSum(Count(A),Count(B))

Hope this help and let me know.

Regards,

Sokkorn  Partner - Champion III

Rereading your post, you say you want to return the value of Count(B) if Count(A) is null (ie not Count(A) + Count(B)?

Then, again to avoid calculating Count(A) twice, you can use

=Alt(Count(A), Count(B), 0)

This will return Count(A) if not null, otherwise Count(B), or zero if both are null.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein Not applicable
Author

Huge thanks bro. Not applicable
Author

Thank you Sokkorn,

Your suggestion is greatly appreciated, but actually the formula I have in hand is much more complicated than count(A)+count(B), that is a simplified scenario that I thought of.

So under my real case, NumSum() is not really applicable since the expression involves weighted average.

Thank you and I will try NumSum() in some future cases definitely.

Regards,

Jason Community Browser