Set analysis : remove the null values

[fredericvillemi]

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

[stigchel · Accepted solution]

Try with

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

[#3]

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]

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

[#5]

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]

you can use nullcount

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

*

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

} MYVALUE)

NULL handling in QlikView

[fredericvillemi]

so easy !

[fredericvillemi]

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