5 Replies Latest reply: Mar 16, 2016 2:18 AM by Saurabh Pandit

# Set Analysis - All values except field X

I have an expresion that includes all the data from set. I'm using Sum( {1} Sales ). Now unfortunatelly I need to modify the expresion to get into consideration all the values from the set except one filter. Any ideas how can I achieve this? I was thinking to use set modifiers or indirect set analysis.

• ###### Re: Set Analysis - All values except field X

If you want to ignore the selections made against one filter, add that into your set modifier to select everything like this (lets assume you want to ignore selections against REGION):

Sum( {1<REGION={'*'}> Sales )

Or, I think this might also work:

Sum( {1<REGION=> Sales )

Regards,

Nigel.

• ###### Re: Set Analysis - All values except field X

try this

sum( {<field = E({1<field={"X"}>})>} Sales)

• ###### Re: Set Analysis - All values except field X

Hi ivozen,

It's surprisingly hard to achieve what you need but this is the way I got it working (assuming I understood your problem correctly):

1. Create a variable, for example v_set_values. Type in this expression and rename 'IncludedFilter' so that it matches your data:

= If(GetSelectedCount(IncludedFilter) = 0,

Concat({1} DISTINCT '"' & IncludedFilter & '"', ','),

'"' & GetFieldSelections(IncludedFilter, '","', 100000) & '"')

This should work with any kind of field value but I wouldn't use this method if the field has a lot if distinct values.

2. In your object you should now be able to use this expression:

= Sum({1< IncludedFilter={\$(v_set_values)} >} SomeValue)

Basically what this does is it calculates the sum of field SomeValue excluding all selections in all fields except IncludedFilter. Was this what you wanted to achieve?

-Teemu

• ###### Re: Set Analysis - All values except field X

If you want to include all field selections except field X, then this is what you want:

SUM({<X=>} Sales

If you want to ignore all field selections except field X, then you want this:

1. Create a variable

vFieldX=       =chr(39)&Concat(DISTINCT X, chr(39)&','&chr(39))&chr(39)

2. Use this expression

SUM({1<X={\$(=vFieldX)}>} Sales)

Similar to Teemu's solution, but with a simpler variable.

• ###### Re: Set Analysis - All values except field X

Hello,

Maybe a simple X::\$X would also work with using 1 for the set analysis.

Sum({1<X::\$X>} Sales)

Regards,

Saurabh