8 Replies Latest reply: May 15, 2013 10:13 AM by David Koschei

# Set Analysis - Sum if two fields null?

counts A when B is null

=count({\$<[A]={"=isnull(B)"}>}[A])

counts A when B is not null

=count({\$<[B]={"*"}>}[A])

And both of these work perfectly.

What I'm trying to do now is count A when both B AND C are null.

=count({\$<[A]={"=isnull(B)","=isnull(C)"}>}[A]) didn't work.

=count({\$<[A]={"=isnull(B)"},[A]={"=isnull(C)"}>}[A]) also didn't work.

Anyone know the syntax? Or is this not possible?

Thank you

• ###### Re: Set Analysis - Sum if two fields null?

Have you tried

=count({\$<[A]={"=isnull(B) and isnull(C)"}>}[A])

• ###### Re: Set Analysis - Sum if two fields null?

Just tried it,

=count({\$<[A]={"=isnull(B) and isnull(C)"}>}[A])

ignores the set, but counts [A]

=count({\$<[A]={"=isnull(B) and =isnull(C)"}>}[A])

returns nothing

• ###### Re: Set Analysis - Sum if two fields null?

try with intersection:

=count({<[A]={"=isnull(B)"}>*<[A]={"=isnull(C)"}>}[A])

• ###### Re: Set Analysis - Sum if two fields null?

No luck either

Returns everything as if the set weren't there

• ###### Re: Set Analysis - Sum if two fields null?

It would do that if that combined set isnt there. are you sure there are cases where both are null?

• ###### Re: Set Analysis - Sum if two fields null?

Yes, but if it matters, they are nulls due to relationships.  A, B, and C are three different tables that are all related on one key field.  The nulls are because that key value doesn't exist in another table.

I'm wondering if creating a fact table would resolve my issue.. may be wise to do that anyway

• ###### Re: Set Analysis - Sum if two fields null?

yes that would be wise, since you are running into the difference between missing values and table nulls.

You can see some about the different use cases here:

http://community.qlik.com/docs/DOC-3155

• ###### Re: Set Analysis - Sum if two fields null?

Ooh, that's a very nice PDF in that URL, thank you.  I was always on to the fact that there were multiple types of "nulls", but that document explains it nicely.  I'll give the fact table a try!