10 Replies Latest reply: Sep 10, 2012 10:41 AM by Michael Solomovich RSS

    Set analysis problem

    Mario Pagel

      Hello everybody,

       

      based on the following table

       

      Shop     Partner     Sales

      a                           100

      a           b               50

      a           c               30

      b                           500

      b           a              200

      b           c              250

      c           a              150   

       

       

      i'd like to show the sum of the sales but internal sales should be eliminated, based on the selection of shops. That means if shop a and b are selected, just the external sales (without partner) and the sales with partner (shop) c should be displayed.

       

      I tried Sum({$<Partner=E({1<Shop={}>}Partner)>}Sales) but it doesn't work.

       

      Any Idea?

       

      Regards

      Mario

       

      Can't anyone help me?

        • Re: Set analysis problem
          Vigneswar Anantharam

          I am not sure if I understood your issue completely.

          But, if you are looking for the below result:

           

          Cal_Dimension.jpg

          Instead of using SET Analysis in Expression, keep it as Sum(Sales) but use Calculated Dimension:

               =If(Partner = '',Shop,Partner)

          • Re: Set analysis problem
            whiteline _

            Hi, socura.

            You can try this expression:

            =Sum({$<Partner={"=not isnull(Partner)"}>} Sales)

            • Re: Set analysis problem
              Mario Pagel

              Hi,

               

              thanks for the answers, but the problem isn't solved. Perhaps I should detail it. The point is to get consolidated results, where the internal sales are eliminated, based on the selection of shops.

               

              If shops a and b are selected:

               

              a     130

              b     750

               

              If just shop a is selected:

               

              a     180

               

              Only shop b is selected:

               

              b     950

               

              Only shop c is selected:

               

              c     150

               

              Shops a, b and c are selected:

               

              a     100

              b     500

              c     -

               

              Shops b and c are selected:

               

              b     700

              c     150

               

              Maybe set analysis is not the solution. What about triggers..?

              • Re: Set analysis problem
                Mario Pagel

                Hi,

                 

                thanks for the answers, but the problem isn't solved. Perhaps I should detail it. The point is to get consolidated results, where the internal sales are eliminated, based on the selection of shops.

                 

                If shops a and b are selected:

                 

                a     130

                b     750

                 

                If just shop a is selected:

                 

                a     180

                 

                Only shop b is selected:

                 

                b     950

                 

                Only shop c is selected:

                 

                c     150

                 

                Shops a, b and c are selected:

                 

                a     100

                b     500

                c     -

                 

                Shops b and c are selected:

                 

                b     700

                c     150

                 

                Maybe set analysis is not the solution. What about triggers..?

                • Re: Set analysis problem
                  Mario Pagel

                  Hi Roberto,

                   

                  while posting my answer an error ocurred and I tried to post it again (and again...). After seeing that the posting was repeated, I tried do delete the multiple ones. Unfortunately your posting too. Sorry, this was my first posting in the community...

                   

                  I stored your solution but if you like to you can repost it.

                   

                  The solution is almost right. There's just one thing: If I don't select any shop, the external sales are missing. But I could solve this by forcing the user to select at least one shop.

                   

                  Thank you very much and greetings from cologne!

                  Mario