Set Analysis - Sum if two fields null?

[#1]

I found this answer elsewhere:

counts A when B is null

=count({$<={"=isnull(B)"}>})

counts A when B is not null

=count({$<={"*"}>})

And both of these work perfectly.

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

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

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

Anyone know the syntax? Or is this not possible?

Thank you

[swuehl]

Have you tried

=count({$<={"=isnull(B) and isnull(C)"}>})

[#3]

Just tried it,

=count({$<={"=isnull(B) and isnull(C)"}>})

ignores the set, but counts

=count({$<={"=isnull(B) and =isnull(C)"}>})

returns nothing

[calvindk]

try with intersection:

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

[#5]

No luck either

Returns everything as if the set weren't there

[calvindk]

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

[#7]

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

[calvindk · Accepted solution]

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

[#9]

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!