5 Replies Latest reply: Feb 27, 2012 10:09 AM by oleg61858 RSS

    Set Analysis logic

      Hi, dear colleagues.
      Could you help me, please.
      I have a table, which contains 2 fields:
      Month          Values
      Jan 2012     100
      Dec 2011     200
      Nov 2011     300
      etc.
      I want to get value of previous month.
      My expression is:
      sum
      ({<Month = {"$(=MonthName(Month, -1 ))"}>}Vallue)
      But it works only if 1 month is selected (like Only(Month)) and doesn't work without any selection.
      Is it possible to calculate dimension for each value of dimension "Month"?
      Thanks a lot.
        • Set Analysis logic
          Michael Solomovich

          Oleg,:

          If you're using in in a table/chart where Month is dimension, try function above(), for example:
          above(sum(Value))

            • Re: Set Analysis logic

              Hi, Michael.

              I thought about it. But my task is more difficult.

              I need to bulid a chart, which shows a difference between current value and last month value.

              Dimension "Month" is limited (last 6 months).

              To limit the dimension i'm using calculated dimension

              =if(Month> MonthStart(Today(), -6), Month)

              if i use above function in expression, value of "Sep 2011" is null, because it don,t see the value of "Aug 2011".

              That's why I,m considering set analysis as a solution.

              It will be correct if I show last 7 month and "Sep 2011" will have not null value. In this case I need to hide "Aug 2011"

               

              Thanks.

            • Re: Set Analysis logic
              John Anderson

              Hi Oleg,

               

              I was thinking it could be done a different way. 

               

              for example:   if you where to add another field in the script which sets a new field to the previous month.

               

              ADDMONTHS([Month],1)  AS PrevMonth,

               

              then use this field in your SetAnalysis, ie: SUM( {< [Month] = P(PrevMonth) >} Values)

               

              that this should work, but preliminary testing (see attachment) did not work.  I'm pretty sure this should work and that I need some refinement of the SetAnalysis.  I'll keep playing as time permits but someone else may be able to advance this idea in the meantime.

               


              Regards,

               

              John.