Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
fredericvillemi
Specialist

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

1 Solution

Accepted Solutions
stigchel
Partner - Master

Try with

=sum({1<PREVIOUS_MONTH={1},SITE={'*'}>} MYVALUE)

View solution in original post

7 Replies
stigchel
Partner - Master

Try with

=sum({1<PREVIOUS_MONTH={1},SITE={'*'}>} MYVALUE)

Not applicable

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

anbu1984
Master III

=sum({1<PREVIOUS_MONTH={1}>} If(Not IsNull(SITE),  MYVALUE))

Not applicable

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

ramoncova06
Partner - Specialist III

you can use nullcount


=sum({1<PREVIOUS_MONTH={1}>

*

<PREVIOUS_MONTH  ={"=nullcount(SITE )=0"}>



} MYVALUE)


NULL handling in QlikView

fredericvillemi
Specialist
Author

so easy !

fredericvillemi
Specialist
Author

Thanks, yes, it was easy but thanks for your explanation