Set Analysis indirect selections

[derekjones]

Hi All

I'm hoping someone can help. I've got an issue using set analysis. What I'm after is to create an expression that sums all of a particular field (ignoring all selections apart from one on the worksheet). I've used the set identifier i.e. '...{1<...' to request a full set of records and then added the indirect set analysis of '...=p()' to include the field I want to use selections for. My issue is that the field in question although not selected in the sheet, if another field restricts the value, it still reduces the data set as if it were selected (an indirect selection of a sort)

I've attached an example to hopefully explain it better.

In my example, I have a dataset which contains:

Category = Product Classification

Vendor = Supplier/Brand

Revenue = Sales Revenue

Area = Sales Area

Now the pie graph I want to build is one where it includes all Vendors (even if one is selected), but is dependent on Category selected and is split by area's of 'My Sales' and ' All Sales' less 'My Sales'. So I have two expressions:

My Sales =sum({1<Area={'My Sales'}, Category=p()>} Revenue)

Other Sales =sum({1<Area={'All Sales'}, Category=p()>} Revenue)-[My Sales]

However if you select a Vendor (use Vendor C as one which highlights the issue well) then because Vendor C has only certain categories in the dataset there is an 'indirect' selection of those categories.

Is there anyway of completely ignoring Vendor selection (either direct through the listbox or indirect) whilst still allowing the user to select category to refine selections?

My thanks in advance for any assistance.

Derek

[derekjones]

Thanks Stefan

Very clever, as far as I can see you are creating individual set analysis exclusions per each field I want to exclude by using a concat.

That works perfectly in the example and looks like it will work in my real world example. Do you think it will have performance issues on a large dataset?

Thanks again.

Derek

[swuehl]

Derek,

indeed a clever way, but not my own original creation.

The expression part containing the dollar sign expansion

[$(=Concat({1<$Field-={'Category', 'Area'}>}distinct $Field,']= ,[')&']=')

should expand to

[OtherType]= ,[Report]= ,[Revenue]= ,[Vendor]=

i.e. clear all fields except the one listed in $Field-={'Category', 'Area'}

(Note the -=  minus equal operator here). $(Field) is a system field containing all your table field names.

I don't think that this will affect your performance, I mean not more than doing the selection in another way.

Regards,

Stefan

[juan_c_martinez]

Very clever!