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

# 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

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

Hi ,

Pfa , Hope this is helpful for you

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

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.

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

attach your sample application

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

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/

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

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