Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Set Analysis - Counting NULL Values

I'm trying to count NULL values using Set Analysis. I basically use this code:

Count({$<[EagleFeedback.Win/Loss]={}>} [EagleFeedback.Win/Loss])

It never returns any values even though I know there are some.

How do I use Set Analysis to count NULL values?

16 Replies
Not applicable

Set Analysis - Counting NULL Values

I was able to get it to work using some of my data with:

Count({$-<[EagleFeedback.Win/Loss]={}>} [EagleFeedback.Win/Loss])


EDIT: I edited my comment. On my first reply, I was using blank value, not null values. This new expression counts everything and then subtracts anything with a valid entry in that field. I think that should give you the count of truly null values.

Not applicable

Set Analysis - Counting NULL Values

I saw something like that too. But I thought "...={}" specifically selected NULL values. That being the case, I would expect the negative sign would EXCLUDE NULL values.

I infer from your comment that "...={}" selects everything. I thought I read in the documentation that "...={}" selects NULL value. Did I read it wrong?

BTW, thanks for you response. It's truly appreciated.

mike_garcia
Valued Contributor

Set Analysis - Counting NULL Values

Maybe something like:

Count({$<[EagleFeedback.Win/Loss]= {$(=null())}>} [EagleFeedback.Win/Loss])


Cheers.

Miguel García
Qlik Expert, Author and Trainer
Not applicable

Set Analysis - Counting NULL Values

Sorry, ...{$(=null())}>} doesn't work.

Also, ({$-<[EagleFeedback.Win/Loss]={}>} from NMiller above doesn't count the NULL values -- it counts ALL (NULL and not NULL); that is, it excludes NOTHING which leaves EVERYTHING.

Thanks for trying Mike.

mike_garcia
Valued Contributor

Set Analysis - Counting NULL Values

Turns out there's a function specifically for this situation... Use:

=NullCount([EagleFeedback.Win/Loss])


Mike.

Miguel García
Qlik Expert, Author and Trainer
Not applicable

Set Analysis - Counting NULL Values

Thanks Mike. I need to figure our how to do this with set analysis. I have a complex (for me) set analysis statement that wasn't working and whittled down the problem to the above statement.

mike_garcia
Valued Contributor

Set Analysis - Counting NULL Values

Just did a little test and this expression worked fine:

Count({$<isnull([EagleFeedback.Win/Loss])= {'-1'}>} [EagleFeedback.Win/Loss])


Mike.

Miguel García
Qlik Expert, Author and Trainer
Not applicable

Set Analysis - Counting NULL Values

I humbly acknowledge the problem discussed in this thread is entirely my fault. But possibly it can be of assistance to others:

WHEN LOOKING FOR NULL VALUES, MAKE SURE NULLS ARE "NULL" AND NOT "-".

Thanks everyone and again, sorry.

Not applicable

Set Analysis - Counting NULL Values

Hi,

I had same problem and my solution was:

COUNT(
{$-<
EagleFeedback.Win/Loss = -{}
>}
EagleFeedback.Win/Loss
)

Regards,

Antanas