3 Replies Latest reply: Aug 17, 2011 5:35 AM by Sam Vile RSS

    Set Analysis

      Morning All,

       

      Just a quickie:

       

      I'm using the attached set analysis to calculate the Actual value for the year 2011. What I want to do is change it, so I can look at the latest months value instead. I'm guessing I need to do something with the max and month function but I can't quite figure it out.

       

      Can anyone help me please?

       

      Sum({$<Year = {2011},Measure={'Actual'}>}Value)

        • Re: Set Analysis
          Martin FAVIER

          Hi,

           

          If you want to work on the latest month, I advice you to do something like this :

           

          Sum({$<Year = {2011},Measure={'Actual'}, Month={"$(=Max(Month))"} >}Value)
          

           

          Or

           

          Sum({$<MonthYear = {"$(=Max(MonthYear))"},Measure={'Actual'}} >}Value)
          

           

          And if you want to work on the three latest months, you have to do :

           

          Sum({$<MonthYear = {">=$(=AddMonths(Max(MonthYear),-2))"},Measure={'Actual'}} >}Value)
          

           

          Hope that helps you

           

          Martin

          • Re: Set Analysis
            Liron Baram

            hei

             

             

            try something like this

             

             

            Sum({$<Year = {2011},Month={"$(=max(Month))"},Measure={'Actual'}>}Value)

             

             

            assunming month is a numeric field

            • Set Analysis

              That does work, I do however have a problem with my data which could be tricky to get around.

               

              The max month will be 12 as there is budget data loaded until the end of the year, this set analysis therefore returns £0 for the actual data because there's only data loaded up Jul (7) for actuals.

               

              Do you know a way around that?!