4 Replies Latest reply: Mar 12, 2018 5:42 PM by Massimo Grossi RSS

    Set analysis - Field = Field

    Yatin Soni

      Hi,

      I am struggling to convert an IF THEN expression to Set Analysis.

      I have a sample below.

      Original expression:

      sum( if(YM = EFFECTIVEYM, Sales))

       

      Set Anaysis expression:

      sum( {$< YM = P(EFFECTIVEYM)  >} Sales)

       

      This set analysis works fine only of there is 1 YM value selected. As soon as I select more than 1 YM value, then the figures are overstated.

      Is there another way of writing set analysis where FIELD = FIELD?

       

      Thanks

      Yatin

        • Re: Set analysis - Field = Field
          Massimo Grossi

          maybe

           

          here, page 14, Using two fields

          Set Analysis: syntaxes, examples

           

           

          or you can add a flag in the script and use that flag in set analysis

            • Re: Set analysis - Field = Field
              Yatin Soni

              Hi,

              I have reviewed this pdf, but don't understand what it means by creating an autonumber() key? How do I create this? Is it something I need to do in the backend script?

               

              Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.

               

              "Attention: the searched dimension cannot be also in the boolean condition. If needed, create an integer key with Autonumber(). "

               

              Thanks.

                • Re: Set analysis - Field = Field
                  Massimo Grossi

                  1)

                  The pdf suggestion works if the flelds are in the same table and

                  you have a primary key in the table; if not, may be you can create in the script a primary key with the autonumber function 

                   

                   

                  2)

                   

                  Also, these 2 fields are in separate tables, so I cannot create a Flag field in the backend.

                   

                   

                  Sometimes is possible if there is a relation between the 2 tables

                   

                      ATable

                       AKey,     YM

                       1,          1-1-2018

                       2,          2-1-2018

                   

                       BTable    

                       AKey,   BKey,     EFFECTIVEYM,     Flag

                       1,         a,           10-1-2018,             

                       1,         b,           1-1-2018,               1     the ATable YM  of AKey 1 is 1-1-2018 = EFFECTIVEYM           

                       2,         c,           1-1-2017,

                       2,         d,           1-1-2017,

                   

                  applymap or a join can be useful to calculate the flag

                   

                   

                  3)

                  sum if can be a problem with many rows (performance)

                   

                   

                  4)

                  maybe you can post your .qvw or a small example

              • Re: Set analysis - Field = Field
                Sunny Talwar

                Do they come from the same table in the back end? If they do... may be create a flag in the script

                If(YM = EFFECTIVEYM, 1, 0) as YMFlag

                 

                and then this

                Sum({$<YMFlag = {'1'}>} Sales)