Problem with Null Values

[#1]

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

[er_mohit]

try this

count(A+B)

hope it helps

see the attached file..

[nirav_bhimani]

HI,

Try this

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

Regards,

Nirav Bhimani

[sujeetsingh]

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

[jonathandienst · Accepted solution]

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

[Sokkorn]

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

[jonathandienst]

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

[#8]

Huge thanks bro.

[#9]

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