Define a set using the isNull operator

[#1]

Hi,

I’m stuck trying to define a set when the using the isNull operator.  I need to get a count of everyone with an email address on file. Many people have more than one address so just counting the emails won’t do.  I need to count the distinct IDs of people with emails.    This is what I currently have, which is throwing an error. Do I need to use if() when using IsNull?

=Count({$<isNull([Email Addr]) = 0>} Distinct([Id Number])

Thanks!

Grace

[giakoum · Accepted solution]

=Count({$<[Email Addr] = {"*"}>} Distinct([Id Number])

[MK_QSL]

=Count({$<[Email Addr] = {'*'}>} Distinct([Id Number])

or

=Count({$<[Email Addr] = {"=LEN(TRIM([Email Addr]))>0"}>} Distinct([Id Number])

[#4]

Hi Grace,

just FYI, a set is in essence a number of selections for the aggregation you are doing, just like with standard user selections, you can't select nulls within set analysis. If you were wanting to look at the flipside of this requirement and see those with no email (ie your nulls at present), you would need to change the nulls to a real value ('NA, 'No email' etc) first in order to use them in a set

hope that helps

Joe

[#5]

Thanks, Joe! That is helpful insight.