
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Set analysis : remove the null values
Hello,
I want to count a value for a specific month tagged PREVIOUS_MONTH=1 but i would like to remove all lines where SITE is null.
How can i do using Set Analysis ?
Example :
=sum({1<PREVIOUS_MONTH={1}>} MYVALUE)
but i want to remove the value where SITE is null
- Tags:
- set analysis
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try with
=sum({1<PREVIOUS_MONTH={1},SITE={'*'}>} MYVALUE)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Frederic,
you can't explicitly exclude nulls in set analysis (unless you clean the nulls to real values), what you can do though is select all values from a field, which will implicitly exclude nulls (as they cannot be selected)
so for your example:
=sum({1<PREVIOUS_MONTH={1}, SITE={'*'}>} MYVALUE)
will exclude the null site values
hope that helps
Joe

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=sum({1<PREVIOUS_MONTH={1}>} If(Not IsNull(SITE), MYVALUE))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I wouldn't recommend using the If inside the Sum like that, you'll be evaluating on a row by row bases rather than a set analysis selection, so performance may suffer.
have a look at this thread by HIC on the subject
https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/30/conditional-aggregations
Joe


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you can use nullcount
=sum({1<PREVIOUS_MONTH={1}>
*
<PREVIOUS_MONTH ={"=nullcount(SITE )=0"}>
} MYVALUE)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
so easy !

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, yes, it was easy but thanks for your explanation
