topic Re: Set Analysis - Sum if two fields null? in QlikView

Set Analysis - Sum if two fields null?

Tue, 14 May 2013 21:02:34 GMT

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

Re: Set Analysis - Sum if two fields null?

swuehl — Tue, 14 May 2013 21:33:01 GMT

Have you tried

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

Re: Set Analysis - Sum if two fields null?

Tue, 14 May 2013 22:16:34 GMT

Just tried it,

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

ignores the set, but counts

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

returns nothing

Re: Set Analysis - Sum if two fields null?

calvindk — Tue, 14 May 2013 23:12:14 GMT

try with intersection:

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

Re: Set Analysis - Sum if two fields null?

Wed, 15 May 2013 13:58:08 GMT

No luck either

Returns everything as if the set weren't there

Re: Set Analysis - Sum if two fields null?

calvindk — Wed, 15 May 2013 13:59:53 GMT

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?

Wed, 15 May 2013 14:03:31 GMT

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?

calvindk — Wed, 15 May 2013 14:07:12 GMT

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?

Wed, 15 May 2013 14:13:41 GMT

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!