topic Re: using isnull() in set analysis in QlikView

using isnull() in set analysis

Wed, 15 Aug 2012 20:00:54 GMT

Hello:

I have an expression using an if statement that I'd like turned into a set analysis statement.

I have two dimensions (store and checknum) and a measure (qty).

I want to show a count of the number of checknums that are null, listed by store.

The following if statement in an expression works, but only when I include the checknum as a dimension

if(isnull(qty)=-1,count(checknum))

if I only have [store] as a dimension, it returns the count of all checknum for that store, since at least one checknum per store has a null.

I would think that this set analysis expression would work, but it is not. count({<if(isnull(qty)={-1}>},checknum) it's saying the syntax is wrong, and returning no results.

Thanks

store	checknum	isnull(qtY)	if(isnull(qty)=-1,count(checknum))
		-1	17
aaa	1	-1
aaa	2	-1
aaa	3	-1
aaa	4	-1	1
aaa	5	-1
aaa	6	-1
aaa	7	-1
aaa	8	-1
aaa	9	-1
bbb	10	-1
bbb	11	-1
bbb	12	-1	1
bbb	13	-1
bbb	14	-1
bbb	15	-1	1
bbb	16	-1
bbb	17	-1

Re: using isnull() in set analysis

Anonymous — Wed, 15 Aug 2012 20:29:49 GMT

use this expression

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

Re: using isnull() in set analysis

Wed, 15 Aug 2012 20:49:18 GMT

Using count({$<qty={"*"}>}checknum) returns the count of checknum when qty is NOT null. I want to see the count of checknum when qty IS null.

I could do the following, which works, but it seems like its an extra step:

count ({$}checknum) - count({$<qty={"*"}>}checknum)

Thanks

Re: using isnull() in set analysis

Anonymous — Thu, 16 Aug 2012 00:38:12 GMT

Maybe this:

count({$<qty-={"*"}>}checknum)

store	checknum	isnull(qtY)	if(isnull(qty)=-1,count(checknum))
		-1	17
aaa	1	-1
aaa	2	-1
aaa	3	-1
aaa	4	-1	1
aaa	5	-1
aaa	6	-1
aaa	7	-1
aaa	8	-1
aaa	9	-1
bbb	10	-1
bbb	11	-1
bbb	12	-1	1
bbb	13	-1
bbb	14	-1
bbb	15	-1	1
bbb	16	-1
bbb	17	-1

store	checknum	isnull(qtY)	if(isnull(qty)=-1,count(checknum))
		-1	17
aaa	1	-1
aaa	2	-1
aaa	3	-1
aaa	4	-1	1
aaa	5	-1
aaa	6	-1
aaa	7	-1
aaa	8	-1
aaa	9	-1
bbb	10	-1
bbb	11	-1
bbb	12	-1	1
bbb	13	-1
bbb	14	-1
bbb	15	-1	1
bbb	16	-1
bbb	17	-1

store	checknum	isnull(qtY)	if(isnull(qty)=-1,count(checknum))
		-1	17
aaa	1	-1
aaa	2	-1
aaa	3	-1
aaa	4	-1	1
aaa	5	-1
aaa	6	-1
aaa	7	-1
aaa	8	-1
aaa	9	-1
bbb	10	-1
bbb	11	-1
bbb	12	-1	1
bbb	13	-1
bbb	14	-1
bbb	15	-1	1
bbb	16	-1
bbb	17	-1