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

    Set Analysis - All values except field X

    Ivaylo Dimov

      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
            Vishwaranjan Kumar

            try this

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

            • Re: Set Analysis - All values except field X
              Teemu Pitkänen

              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
                Jerry DiMaso

                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.