
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count distinct numbers and excluding records with Set Analysis
Dear all,
I am still new to the set analysis and this syntax really drives me crazy 😉
from the following table (extract from qvd-file) I want to count all distinct subjids which are no screen failures:
SUBJID | ENSTATUS | TERMINATION_TYPE |
X004-001 | terminated | Premature (screen failure) |
X004-002 | terminated | Premature (screen failure) |
X004-003 | terminated | Premature (screen failure) |
X004-004 | terminated | Premature (screen failure) |
X004-005 | enrolled | |
X004-006 | enrolled | |
X004-007 | terminated | Premature (screen failure) |
X004-008 | terminated | Premature (screen failure) |
X004-009 | terminated | Premature (screen failure) |
X004-010 | terminated | Premature (screen failure) |
X009-001 | enrolled | |
X009-002 | enrolled | |
X009-003 | enrolled | |
X009-004 | terminated | Premature (screen failure) |
X009-005 | enrolled | |
X009-006 | enrolled | |
X009-007 | enrolled | |
X009-008 | enrolled | |
X009-009 | terminated | Premature (screen failure) |
Counting all premature termination works:
Count({<TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)
the other way around I've read would be with -= but this doesn't work and always return 0
Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)
even more surprisingly to me counting the non-terminated works as expected:
Count({<ENSTATUS -= {'terminated'}>} distinct SUBJID)
So, why are the excluded TERMINATION_TYPE records are not counted?
Thanks,
Marc
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is your NULLs that is causing you trouble with SET analysis.
Try one of these two.
=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)
=Count({$-$<TERMINATION_TYPE ={'Premature (screen failure)'}>} distinct SUBJID)
Qlik Community MVP


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There's probably some fancy syntax you could use to make this work, but I recommend changing your load script so the TERMINATION_TYPE all have values. Something like:
load
If(isnull(TERMINATION_TYPE),''No Failure',TERMINATION_TYPE) as TERMINATION_TYPE,
Then use set analysis to count TERMINATION_TYPE={'No Failure'}

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It is your NULLs that is causing you trouble with SET analysis.
Try one of these two.
=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)
=Count({$-$<TERMINATION_TYPE ={'Premature (screen failure)'}>} distinct SUBJID)
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=Count({$< SUBJID = E({<TERMINATION_TYPE ={'Premature (screen failure)'} >}SUBJID)>} distinct SUBJID)
This seems to work. Thanks a lot. "E" stands for Exclude? As I said before, I am really struggling with the syntax of the set analysis...
Thanks,
Marc

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
well, this would be wrong... The termination type is not "No Failure" they are simply not terminated. The source data contains a lot of conditional answers where a lot of values missing, since these depend on other values. (e.g. in the database the question regarding termination type is disabled until the question Termination is not ticked). If I will need to fill all these missing values with some "dummy" values is a lot of work which then also need to be excluded in other counts.
Nevertheless, your proposed solution is working, though.
Thanks,
Marc

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count({<TERMINATION_TYPE -= {'Premature (screen failure)'}>} distinct SUBJID)
The above doesn't work, because most probably your field's other values are NULLs. And nulls in set analysis is not considered to be part of ALL values or '*'. Here minus operator looks for other values except nulls. Therefore to handle NULL cases like yours, try like:
Count({$ - <TERMINATION_TYPE = {'Premature (screen failure)'}>} distinct SUBJID)
Or, you might want to replace '$' with '1' if you want the result ignorant to selections.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes. You are selecting the SUBJID that are not associated with the 'Premature...' termination type.
Qlik Community MVP
