Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis - Sum if two fields null?

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

1 Solution

Accepted Solutions
calvindk
Contributor III

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

8 Replies
MVP
MVP

Re: Set Analysis - Sum if two fields null?

Have you tried

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

Not applicable

Re: Set Analysis - Sum if two fields null?

Just tried it,

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

ignores the set, but counts

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

returns nothing

calvindk
Contributor III

Re: Set Analysis - Sum if two fields null?

try with intersection:

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


Not applicable

Re: Set Analysis - Sum if two fields null?

No luck either

Returns everything as if the set weren't there

calvindk
Contributor III

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?

Not applicable

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

calvindk
Contributor III

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

Highlighted
Not applicable

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!