5 Replies Latest reply: Jul 6, 2011 4:35 PM by Deepak Vadithala RSS

    Set Analysis Issue

    Luca Cavallari

      Hello everyone,

      i have an issue that i dont really know how to solve with set analysis:

       

      i need to calculate an expression like sum(value) not affected by any selection, (so i would normally use a {1} in the expression)   except for Field1 and Field2,which selections should affect the expression. how could i do this??

       

      Thank you in advance

        • Set Analysis Issue
          Erich Shiino

          Hi,

          You will need to include all your fields (expect the ones you want to consider) in set analysis, like this:

          sum( {$<Field3 = , Field4 = , Field5 = >}value) // This will ignore selections in fields Field3, Field4 and Field5

           

          Hope this helps,

           

          Erich

            • Set Analysis Issue
              Luca Cavallari

              Thank you Erich, this is how i would normally fix the problem, but since i would have to add hundres of fields like this, i was wondering if there was a faster way to get the same result, something like {1-<Field1, Field2>} even if this expression doesnt work well...

                • Set Analysis Issue

                  In that case, I would recommend that you develop a script to create the expression.  Then, paste the expression into QlikView.  Something quick in Python, AutoHotkey, Ruby, or your favorite programming language should do the trick.

                  • Set Analysis Issue
                    Erich Shiino

                    You can use an expression to write this sequence of fields for you.

                    You can create a variable vText with the following expression:

                    =Concat( {<$Field-={'Field1','Field2'}>} '['& $Field & ']= ',',' ) //dont forget the =

                    This expression will not include Field1 and Field2 in the string.

                    Then, on set analysis, it will be:

                    sum {<$(vText)>} value)

                     

                     

                    Hope this helps,

                     

                    Erich

                      • Re: Set Analysis Issue
                        Deepak Vadithala

                        Hi Mate,

                         

                        Erich solution works and I had just improved it to make it generic.

                         

                        =COUNT({$<[$(=Concat({1<$Field-={'Gender'}>}distinct $Field,'],[')&']=')>} Resp_ID)

                         

                        In the above example except {'Gender'} all the other fields won't allow selections. You can seperate with comma to include more field names.

                         

                        I hope this helps!

                         

                        Cheers - DV