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

[#1]

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 YEAR	SALES
Apr-2011	450
May-2011	230
Jun-2011	233
Jul-2011	302
Aug-2011	420
Sep-2011	230
Oct-2011	800
Nov-2011	820
Dec-2011	920
Jan-2011	800
Feb-2011	810
Mar-2011	830
Apr-2012	305
May-2012	350
Jun-2012	298
Jul-2012	150
Aug-2012	289
Sep-2012	380
Oct-2012	744
Nov-2012	730
Dec-2012	700
Jan-2012	750
Feb-2012	737
Mar-2012	644

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-YEAR	AVERAGE SALES
Apr-2012	813.33
May-2012	648.33
Jun-2012	495
Jul-2012	317.67
Aug-2012	266
Sep-2012	245.67
Oct-2012	846.67
Nov-2012	846.67
Dec-2012	846.67
Jan-2012	813.33
Feb-2012	813.33
Mar-2012	813.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-YEAR	AVERAGE SALES
May-2012	648.33

Selections: Year = 2012, Month = Oct

MONTH-YEAR	AVERAGE SALES
Oct-2012	846.67

Selections: Year = 2012, Month = Mar

MONTH-YEAR	AVERAGE SALES
Mar-2012	813.33

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

Thanks,

Bill

[#2]

Hi ,

Pfa , Hope this is helpful for you

[#3]

Hi,

Thanks for your suggestion,

but I want to get the average of last 3 months (but last three months is depends on the condition I had stated above)

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)

Please consider that the first month of the year is Apr

Thanks again.

[#4]

attach your sample application

[#5]

Hi,

may be, the attached file can help, please have a look at it.

I assumed that data is ordered and imported it as is.

Regards,

http://quickdevtips.blogspot.com/

[#6]

Hi Bilge,

You're almost there, somehow, can you able to get the average of last year (Oct, Nov, Dec) and (Jan, Feb, Mar), when selecting these months...

Say, today is 2012, and you

select October 2012, meaning the average for is the result average of (avg(Oct2011+Nov2011+Dec2011)

select November 2012, meaning the average for is the result average of (avg(Oct2011+Nov2011+Dec2011)

select December 2012, meaning the average for is the result average of (avg(Oct2011+Nov2011+Dec2011)

-most likely Oct, Nov, Dec has the same average result.

This is also happen to Jan, Feb, Mar (same computation), you have to based to last years average.

select Jan 2012, meaning the average for is the result average of (avg(Jan2011+Feb2011+Mar2011)

select Feb 2012, meaning the average for is the result average of (avg(Jan2011+Feb2011+Mar2011)

select Mar 2012, meaning the average for is the result average of (avg(Jan2011+Feb2011+Mar2011)\

Only these six month has different computation, the rest of the months, is really getting the average of last 3 months.

Thanks for your followup help.

Regards,

Bill