topic Re: Set Analysis Exclude and Include null in QlikView

Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 10:08:05 GMT

Hi All,

Have a requirement is that,

expression:

Sum{$<[Group]={'*'}-{'I'}>}(Sales)

As per the above expression, we are getting sum(sales) where Group I were excluded.

But the issue is that null values were not getting included.

I want to exlude Group I and include null as well.

Is there a way using set analysis.

Re: Set Analysis Exclude and Include null

anbu1984 — Wed, 24 Sep 2014 10:29:29 GMT

=Sum{$<[Group]={'*'}-{'I'}>}(Sales) + Sum{1-<[Group]={'*'}>}(Sales)

Sum{1-<[Group]={'*'}>}(Sales) - This gives sum of sales where Group is null

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 11:47:05 GMT

Thanks for your reply.

But my issue is that, i want to exclude Group I.

But as per the set analysis it is excluding I as well as null values. because null it will not take as characters.

I want even null values + exclude Group I

Re: Set Analysis Exclude and Include null

anbu1984 — Wed, 24 Sep 2014 12:03:58 GMT

So you want to exclude Nulls and 'I' from Group?

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 12:06:32 GMT

No Include null and exclude I

Re: Set Analysis Exclude and Include null

sudeepkm — Wed, 24 Sep 2014 12:09:16 GMT

You may try capturing the NULL at script and mark them as 'NA' for your dimension field. Then it will be easy to use it in Set analysis

Re: Re: Set Analysis Exclude and Include null

anbu1984 — Wed, 24 Sep 2014 12:09:30 GMT

Sum({$<Group={'*'}-{'I'}>}Sales) -- Sum of Sales after excluding only Group I (Nulls are included)

Sum({$<Group={'*'}-{'I'}>}Sales) - Sum({1-<[Group]={'*'}>}Sales) -- Sum of Sales after excluding where Group is I or Null

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 12:11:51 GMT

Sum({$<Group={'*'}-{'I'}>}Sales) it is excluding both I and null values.

Checked already.

Re: Set Analysis Exclude and Include null

tobias_klett — Wed, 24 Sep 2014 12:13:43 GMT

Hi,

why don't you use two expressions:

Sum({<Group=>}Sales) - Sum({<Group={'I'}>}Sales)

Re: Re: Set Analysis Exclude and Include null

anbu1984 — Wed, 24 Sep 2014 12:16:43 GMT

Set this in the script

Set NullInterpret = '';

Then try this

=Sum{$<[Group]={'*'}-{'I'}>}(Sales) + Sum{1-<[Group]={'*'}>}(Sales)

Re: Set Analysis Exclude and Include null

Wed, 24 Sep 2014 12:21:20 GMT

It will exclude your Group I

Sum({<[Group]={'*'}-{'I'}>}sales)

Re: Set Analysis Exclude and Include null

tobias_klett — Wed, 24 Sep 2014 12:26:48 GMT

isn't that what it says? to exclude Group one and include null?

Re: Re: Re: Set Analysis Exclude and Include null

anbu1984 — Wed, 24 Sep 2014 12:30:51 GMT

Check this qvw

Re: Set Analysis Exclude and Include null

ashfaq_haseeb — Wed, 24 Sep 2014 12:36:33 GMT

Hi,

Try like this

Sum({$<Group={'*'}+{''}-{'I'}>}Sales)

Regards

ASHFAQ

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 12:55:52 GMT

it is excluding null as well

Re: Set Analysis Exclude and Include null

ashfaq_haseeb — Wed, 24 Sep 2014 12:57:02 GMT

Hi,

Did you try my solution.

Regards

ASHFAQ

Re: Set Analysis Exclude and Include null

simondachstr — Wed, 24 Sep 2014 12:58:53 GMT

Sum{$<[Group]-={'I'}>} Sales)

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 13:02:14 GMT

Its not working

Re: Set Analysis Exclude and Include null

ashfaq_haseeb — Wed, 24 Sep 2014 13:05:12 GMT

Hi,

I tried its working fine.

Can you post sample application.

Regards

ASHFAQ

Re: Set Analysis Exclude and Include null

vardhancse — Wed, 24 Sep 2014 13:05:22 GMT

Created one field in the backed script to satisfy the required condition and used that field in the front end.

if(isnull([Group]) or [Group]='E',1,0) as Group_1,

And used Group_1=1 condition in the expression.

Sum{$<[Group]={1}>}(Sales)

So that Only Group I was excluded and included Group E along with null values.

My issue is resolved