11 Replies Latest reply: Jan 11, 2012 9:33 AM by Stefan Wühl RSS

    Set Analysis indirect selections

    Derek Jones

      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

        • Set Analysis indirect selections
          Kaushik Solanki

          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

          • Set Analysis indirect selections
            Stefan Wühl

            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

              • Re: Set Analysis indirect selections
                Derek Jones

                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

              • Set Analysis indirect selections
                Sunil Chauhan

                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

                  • Re: Set Analysis indirect selections
                    Derek Jones

                    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

                      • Re: Set Analysis indirect selections
                        Stefan Wühl

                        Try this:

                         

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

                         

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

                          • Re: Set Analysis indirect selections
                            Derek Jones

                            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

                              • Re: Set Analysis indirect selections
                                Stefan Wühl

                                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

                                 

                                 

                                  • Set Analysis indirect selections
                                    Derek Jones

                                    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

                                      • Set Analysis indirect selections
                                        Stefan Wühl

                                        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