topic Re: Set analysis : remove the null values in QlikView

Set analysis : remove the null values

fredericvillemi — Thu, 28 May 2015 13:11:15 GMT

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

Re: Set analysis : remove the null values

stigchel — Thu, 28 May 2015 13:13:45 GMT

Try with

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

Re: Set analysis : remove the null values

Thu, 28 May 2015 13:14:55 GMT

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

Re: Set analysis : remove the null values

anbu1984 — Thu, 28 May 2015 13:43:59 GMT

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

Re: Set analysis : remove the null values

Thu, 28 May 2015 13:50:53 GMT

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

Re: Set analysis : remove the null values

ramoncova06 — Thu, 28 May 2015 14:04:23 GMT

you can use nullcount

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

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

} MYVALUE)

NULL handling in QlikView

Re: Set analysis : remove the null values

fredericvillemi — Thu, 28 May 2015 14:09:03 GMT

so easy !

Re: Set analysis : remove the null values

fredericvillemi — Thu, 28 May 2015 14:09:58 GMT

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