Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)) | count({<if(isnull(qty)={-1}>},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 |
use this expression
count({$<qty={"*"}>}checknum)
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
Maybe this:
count({$<qty-={"*"}>}checknum)