topic Re: Count Multiple Conditions in QlikView

Count Multiple Conditions

Anonymous — Thu, 19 Nov 2015 14:53:57 GMT

Hello,

This expression is building off one of my previous ones, but I'm making sure that I have the syntax right. The conditions should be:

1. STATUS_TITLE must be one of these: 'Pending Certification', 'Pending Completion', 'Pending Qualification'

2. CERTIFICATION_DATE must be either: ' ' or '0'

If these two are met, then it should count RTC_TITLE.

This is my equation so far:

=Count({$<STATUS_TITLE = {'Pending Certification', 'Pending Completion', 'Pending Qualification'}, CERTIFICATION_DATE = {'', '0'} >} RTC_TITLE)

It keeps throwing straight 0's though which is not correct.

Any help is appreciated!

Thanks!

Re: Count Multiple Conditions

swuehl — Thu, 19 Nov 2015 16:32:16 GMT

Start testing each filter on its own:

=Count({$<STATUS_TITLE = {'Pending Certification', 'Pending Completion', 'Pending Qualification'} >} RTC_TITLE)

=Count({$<CERTIFICATION_DATE = {'', '0'} >} RTC_TITLE)

I assume the second is returning zero. Are you sure you want to filter on 0 and on ' ' (is it a single space?)?

Re: Count Multiple Conditions

Anonymous — Thu, 19 Nov 2015 19:47:41 GMT

Hi Swuehl,

The first filter works with no issues. As you said, the second filter is throwing the errors. Basically I want to count when the STATUS_TITLE is one of those options as well as when the CERTIFICATION_DATE is equal to null or 0. There is a single space in between ' ' currently.

Thanks,

Jordan

Re: Count Multiple Conditions

swuehl — Thu, 19 Nov 2015 22:28:48 GMT

Do you have records with CERTIFICATION_DATE equals 0? Then you should see a count >0.

NULL cannot be selected directly. I would suggest replacing NULL with a value in the script.

Depending on your data model and your requirement, maybe something described here may also help:

NULL handling in QlikView