2 Replies Latest reply: Oct 25, 2012 10:32 AM by Diego Fernando Caivano RSS

    Problem with If instruction

    steve peroni

      Dear all,

      I'm becaming crazy. How don't understand where is my mistake

      
       IF (sum({<MY_YEAR={$(=Only(MY_ANNO-1))}>} MY_SALES) = 0,1, MY_MARGIN/sum({<MY_YEAR={$(=Only(MY_ANNO-1))}>} MY_SALES))
      

       

       

      By the script above, I would obtain the following scenario:

       

      if sum( ..... MY_SALES) = 0 , put MY_MARGIN = 1, else do the margin calculation.

       

      But it doesn't work

      Help please

      Thanks in advance

        • Re: Problem with If instruction
          Gysbert Wassenaar

          I don't see anything wrong with it. If you select a value for MY_ANNO then you should get a result as long as there is data where MY_YEAR = MY_ANNO - 1

          • Re: Problem with If instruction
            Diego Fernando Caivano

            Without knowing your data structure, I guess you should add some operation to MY_MARGIN field except it returns one value only. Otherwise you cannot divide an array of possible values by a unique SUM() of any other field.

             

            Also, try using the ONLY() function with just the field as parameter, keeping the "-1" outside.

             

            Let us know if the following expression works fine:

            IF (SUM({<MY_YEAR={$(=ONLY(MY_ANNO)-1)}>} MY_SALES) = 0,

                 1,

                 SUM(MY_MARGIN)/SUM({<MY_YEAR={$(=ONLY(MY_ANNO)-1)}>} MY_SALES)

            )

             

            Regards.