Set Analysis Exclude and Include null

[vardhancse]

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.

[anbu1984]

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

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

[vardhancse]

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

[anbu1984]

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

[vardhancse]

No Include null and exclude I

[sudeepkm]

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

[anbu1984]

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

[vardhancse]

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

Checked already.

[tobias_klett]

Hi,

why don't you use two expressions:

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

[anbu1984]

Set this in the script

Set NullInterpret = '';

Then try this

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