KPI - set analysis using not NULL

[iliketurtles]

I was hoping to get a little help on using a KPI chart and set analysis.   

I have a single table of data that I am looking to analyze.  In summary the structure I am interested in is:

InquiryID

LetterPrefix

MailDate

I want to count InquiryID when LetterPrefix = "some string value" AND MailedDate is NOT Null

I have specifically created this in my expression builder:  

count(distinct{<letterprefix={'E_A_SVC_G_EXT'}*<maildate-={"*"}>}[INQUIRYID-inquiryid]

This expression shows as 'OK" in the expression builder status which I assume is a good thing.   Yet when I look at the visualiziation of a bar graph there is no data present. 

When I only use this this expression:

count(distinct{<letterprefix={'E_A_SVC_G_EXT'}>}[INQUIRYID-inquiryid])     -  I get results and the graph visually looks correct.  These results, however, are too high since it includes records with NULL maildates. 

My question is, am I using the correct syntax when I attempt to exclude NULL values?  Any feedback is appreciated.  

[hopkinsc]

try..

count(distinct {<letterprefix={'E_A_SVC_G_EXT'}, maildate-={"*"}>}[INQUIRYID-inquiryid])

see if that works

[hopkinsc · Accepted solution]

actually i have just noticed the -=, that will minus every value. so you're basically telling it to exclude all values. 

remove the - and try that.

or

another way would be to give the NULL values a value. This will need to be done in the script. Load in the table that contains the MailedDate field (resident from the original table) and use an IF statement on the MailedDate..

if(IsNull(MailedDate ),'X',MailedDate ) as MailedDate

Then you can use the -={'X'}

I have also just noticed that in your original post you have stated that the field name is MailedDate but in your set analysis you have maildate. is that just a typo in this discussion or is that the problem?  🙂

[iliketurtles]

Thanks for the suggestions and ideas.  Will try them now.  Yes the field name was a typo.