topic Re: KPI - set analysis using not NULL in QlikView

KPI - set analysis using not NULL

iliketurtles — Wed, 06 Mar 2019 17:38:53 GMT

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.

Re: KPI - set analysis using not NULL

hopkinsc — Wed, 06 Mar 2019 17:49:32 GMT

try..

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

see if that works

Re: KPI - set analysis using not NULL

hopkinsc — Wed, 06 Mar 2019 18:15:20 GMT

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.

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? 🙂

Re: KPI - set analysis using not NULL

iliketurtles — Thu, 07 Mar 2019 00:58:19 GMT

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