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

[kaushiknsolanki]

Hi,

     If you want to ignore any selection in set analysis you will be doing that like shown below example.

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

     Here as you can see the vendors has assigned nothing, it means all the selections of vendors will be ignored.

     Hope this will help you.

Regards,

Kaushik Solanki

[swuehl]

Instead of using the set identifer 1 (to request a full set of records) and then trying to set an appropriate selection on the fields needed, you could also specify a set identifier $ (current selection) and clear the selection on fields you want to disregard (or set the selection to other values):

Not sure if I got your requirements correctly, but try:

=sum({$<Area={'My Sales'}, Vendor= >} Revenue)

resp.

=sum({$<Area={'All Sales'},Vendor= >} Revenue)-[My Sales]

'Vendor= ' is clearing the selections in field Vendor.

Hope this helps,

Stefan

[SunilChauhan]

try this

My Sales =sum({$<Area={'My Sales'}, Vendor= >} Revenue)

Other Sales =sum({1<Area={'All Sales'},Vendor= >} Revenue)-sum({1<Area={'My Sales'},Vendor = >} Revenue)

hope this helps

[derekjones]

Hi Kaushik

Sorry, that has not worked, I've amended my example to your format and as you can see, if you select Vendor C, the pie chart changes still

[derekjones]

Hi Stefan

Yes that works for this simle example, what I omitted to mention is that in my production application there are many other fields/tables, so I have to use a set identifier to exclude all field selections otherwise I would have to mention in the set analysis each individual field I want to exclude.

Not sure if this is possible?

Thanks

Derek

[derekjones]

Hi Sunil

As per Stefan's answer, I didn't explain, that the example I've attached only contains the specific data to this issue. The actual dataset has many other fields, I need to use a set identifier of 1 to exclude all selections as trying to exclude all individually would not be possible (well possible, but that would be one big Set Analysis!).

Attached is your solution, but with an additional field of 'Other Type' added as an example of other fields which would be in the dataset, imagine over 100 other fields, how can you exclude all but Category selection?

Thanks

Derek

[swuehl]

Try this:

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

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

[derekjones]

Hi Stefan

Thanks for the second attempt, works agin on the simple example I originally posted, but using this example bringing in a further field (OtherType), you can see by the example attached, when Vendor C and Type B is selected, the pie chart does still change. I'm trying to achieve the pie chart to ignore all selections (imagine 100 other fields in my real app!) apart from Category.

thanks again

Derek

[swuehl · Accepted solution]

Try

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

resp.

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

edit:

attached modified sample