5 Replies Latest reply: Mar 21, 2013 9:06 PM by Bill Ringer Salalima RSS

    How to get the average of previous last 3 months in Set Analysis when not using Straight or Pivot Table?

      Hi Guys,

       

      I got problem, I can't able to get the average Sales for last 3 months in Set Analysis without using accumulation function in straight table or pivot, so that whenever I select only one month, I can still get the average sales.

       

      I have a Fiscal Calendar, the starting Month of the year is Apr, and ends Mar next year.

       

      Say I have this data,

       

       

      MONTH YEARSALES
      Apr-2011450
      May-2011230
      Jun-2011233
      Jul-2011302
      Aug-2011420
      Sep-2011230
      Oct-2011800
      Nov-2011820
      Dec-2011920
      Jan-2011800
      Feb-2011810
      Mar-2011830
      Apr-2012305
      May-2012350
      Jun-2012298
      Jul-2012150
      Aug-2012289
      Sep-2012380
      Oct-2012744
      Nov-2012730
      Dec-2012700
      Jan-2012750
      Feb-2012737
      Mar-2012644

       

      Here's how to get the average sales for each Month:

       

      Say for example: the Current Year is 2012

       

      Average Sales for Apr-2012 is (Average Sales of Jan-2011, Feb-2011, Mar-2011)

      Average Sales for May-2012 is (Average Sales of Feb-2011, Mar-2011, Apr-2012)

      Average Sales for Jun-2012 is (Average Sales of Mar-2011, Apr-2012, May-2012)

      Average Sales for Jul-2012 is (Average Sales of Apr-2012, May-2012, Jun-2012)

      Average Sales for Aug-2012 is (Average Sales of May-2012, Jun-2012, Jul-2012)

      Average Sales for Sep-2012 is (Average Sales of Jun-2012, Jul-2012, Aug-2012)

       

      Somehow, when it comes to the average Sales for Oct, Nov, Dec, Jan, Feb, Mar, these months will always get the last year average sales.

       

      Average Sales for Oct-2012 is (Average Sales of Oct-2011, Nov-2011, Dec-2011)

      Average Sales for Nov-2012 is (Average Sales of Oct-2011, Nov-2011, Dec-2011)

      Average Sales for Dec-2012 is (Average Sales of Oct-2011, Nov-2011, Dec-2011)

      Average Sales for Jan-2012 is (Average Sales of Jan-2011, Feb-2011, Mar-2011)

      Average Sales for Feb-2012 is (Average Sales of Jan-2011, Feb-2011, Mar-2011)

      Average Sales for Mar-2012 is (Average Sales of Jan-2011, Feb-2011, Mar-2011)

       

      Applying the formula above, the result should be like this (see below).

       

      MONTH-YEARAVERAGE SALES
      Apr-2012813.33
      May-2012648.33
      Jun-2012495
      Jul-2012317.67
      Aug-2012266
      Sep-2012245.67
      Oct-2012846.67
      Nov-2012846.67
      Dec-2012846.67
      Jan-2012813.33
      Feb-2012813.33
      Mar-2012813.33

       

      So example I will select only one month, when applying the formula, I can still get the average Sales (see below)

       

      Selections: Year = 2012, Month = May

      MONTH-YEARAVERAGE SALES
      May-2012648.33

       

      Selections: Year = 2012, Month = Oct

      MONTH-YEARAVERAGE SALES
      Oct-2012846.67

       

      Selections: Year = 2012, Month = Mar

      MONTH-YEARAVERAGE SALES
      Mar-2012813.33

       

       

       

       

      Your help is much appreciated. Please help me meet this requirement. I really need to get this.

       

       

      Thanks,

      Bill