Count with conditions

[Ciara]

Hi All

Obviously, I'm new to Qlik and I've been going through lots of posts on the forum.  Some have really helped but you really have to know what you're looking for.  I've looked for possible answers to my question and I've tried out various different syntax but to no avail so I'm posting my very first question hoping someone can help. 🙂

I have a KPI that is counting the distinct AccountNumbers that have any of the below admission assessments (NewValue)

Count({<NewValue = {'NURADMITV-02', 'NURHCASSESLONG6',  'NURHCICUADMITS2',  'NURHCPAED3', 'NURHCSHORTADM6'}>} Distinct AccountNumber) 

What I would like to do is count the number of distinct AccountNumbers that have an AdmitDateTime but do not have an admission assessment.

Can anyone help?

Thanks

Ciara

[treysmithdev]

You can try something like this:

Count({<NewValue = {'NURADMITV-02', 'NURHCASSESLONG6',  'NURHCICUADMITS2',  'NURHCPAED3', 'NURHCSHORTADM6'}, AdmitDateTime = {"*"}, AdmissionAssessment -= {"*"}>} Distinct AccountNumber) 

Doing {"*"} is essentially say give be all values that are not NULL.

-= is saying Does NOT Equal. 

*Note that sometimes the syntax editor will show an error even though it is valid syntax.

If that doesn't work, you can try this instead:

Count({<NewValue = {'NURADMITV-02', 'NURHCASSESLONG6',  'NURHCICUADMITS2',  'NURHCPAED3', 'NURHCSHORTADM6'}, AdmitDateTime = {"*"}> - <AdmissionAssessment = {"*"}>} Distinct AccountNumber) 

 Doing it in the above way is saying give me a set that the NewValue is in this list and has a AdmitDateTime and are not in the following set which has an AdmissionAssement.

[Ciara]

Thanks so much Trey.  I didn't know about the {"*"} so that's great to know and will really help with other measurements.

I think I didn't explain my query very well.

NURADMITV-02, NURHCASSESLONG6 etc are the admission assessments (NewValue).  However, an AccountNumber can have any number of other assessments documented that are not on the list I gave ie NURACTIVITY, NURFRA etc.

So I was trying to ascertain how many AccountNumbers have an AdmitDateTime but don't have one of the admission assessments listed.  The AccountNumber could have other assessments but if they don't have one of the predefined assessments and they do have an AdmitDateTime then I want to count it.