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,
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).
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
Selections: Year = 2012, Month = Oct
Selections: Year = 2012, Month = Mar
Your help is much appreciated. Please help me meet this requirement. I really need to get this.