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

    Set Analysis

    Samuel Vile

      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



          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)




          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



          • Re: Set Analysis
            Liron Baram




            try something like this



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



            assunming month is a numeric field

            • Set Analysis
              Samuel Vile

              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?!