4 Replies Latest reply: May 20, 2011 8:13 AM by Grégory Nallet RSS

    Problem with Set Analysis

      Hi,

       

       

       

      I have a problem with Set analysis.

      I try to calculate, in a pivot table, the sum of costs values (CO_INT_ACTUAL) for the selected year (Y), for the next year (Y+1), and for all the years after.

       

      My totals for columns "selected year" and "next year" and next year works using this formula.

      =Sum({$ <Year={$(=Only(Year)) [CO_INT_ACTUAL] )

       

      To calculate the sum for all the years after, i use this formula:

      =Sum({$<Year={$(>Only(Year+1))}>} [CO_INT_ACTUAL])

      But it doesn't work.

      Can anybody help me please?

      Thx,

      Greg

        • Problem with Set Analysis
          Erich Shiino

          Hi,

          Not sure if I understood the dynamic of the user selections in your case,

          but from a syntax point of view, your expression should be more like this:

           

          =Sum({$<Year={">$(Only(Year)+1)"}>} [CO_INT_ACTUAL])

          or

          =Sum({$<Year={">$(=Only(Year)+1)"}>} [CO_INT_ACTUAL])

           

          Regards,

           

          Erich

          • Problem with Set Analysis

            Hi Greg,

             

            Try below syntax.

             

            sum({<Year={$(=only(Year))}>}[CO_INT_ACTUAL])

             

            sum({<Year={'>$(=only(Year)+1)'}>}[CO_INT_ACTUAL])

             

            Cheers.

            • Re: Problem with Set Analysis

              Do not use set analysis, use a calculated dimension and a variable containing the base year.

               

              I would do this:

              1 - create a variable vBaseYear

              2 - create an input box for the variable

              3 - create a chart calculated dimension as this (untested, check the parens):

                        =If(Year = vBaseYear, 'Y', If(Year = vBaseYear + 1, 'Y+1', If(Year > vBaseYear + 1, 'After', Null())))

               

              This should give you a dimension containing 'Y' for rows pertaining vBaseYear, 'Y+1' for the next one and 'After' for years in the future.

              Selecting 'Ignore if value is null' will delete every row associated with years < vBaseYear.

               

              Good luck!

              • Problem with Set Analysis

                Hi Shyam,

                thx a lot for the good Syntax. In my Set Analysis User Guide, there are no quotes in the formula...

                Thanks Chiesa and Erich for your answers (i didn't need to test it but they are probably good too).

                Bye ;-))