Running average of previous 3 months

[yuvraj_33]

Dear all, need a bit of help on this one...

I have 2 columns Month & sales.

Required a third column that has average of previous three months...

E.g.: today is 03-03-2015.

So, previous 3 months average would include average of current month March & previous 2 months that is Jan & Feb.

Also the constraint is that all the records must have the corresponding values and should change on selections.

For reference attaching excel that consists of two sheets.

One contains data & second sheet shows the required output.

Kindly guide on the same...

[Gysbert_Wassenaar]

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

[#3]

Is this what you require.????..Check out the sheet 1 of the application.

[mukesh24]

Hi Yuvraj,

In backend logic u need to do -->

Order by Year,Month

Read previous 3 record from current row logic.

Regards,

Mukesh Chaudhair

[yuvraj_33]

How can I also achieve the average of previous three months on selections....

Required output is as shown below...

Tried using: RangeSum(above(sum(Amount),0,3))/3

But the issue is on selection of a particular Month, it shows only one record, whereas the requirement is that on selection of a particular Month, it should show the averages of previous 3 Months from selected Month upto the start month in the Month field and not just the selected Month record.

Avail Fields: Month, Sales.

Required Output: AvgSales Field

[yuvraj_33]

Ok.. i'll try with the same...

[jagan]

Hi,

Try like this

Create a variable

vLast3MonthsAvgSales = Sum({<Year=, Month=, Date={'>=$(=MonthStart(Min(Date), -2))<=$(=Date(Min(Date)))'}>} Sales) / Count({<Year=, Month=, Date={'>=$(=MonthStart(Min(Date), -2))<=$(=Date(Min(Date)))'}>} DISTINCT Month)

Note : in the above expression replace Year=, Month=, Date= with your Year, Month and Date field names.

Now in chart use below expression

=If (RowNo()  = 1, vLast3MonthsAvgSales, RangeSum(above(sum(Amount),0,3))/3)

Hope this helps you.

Regards,

Jagan.

[#8]

Also we can approach

In the chart expression,

If(sales>1300,if(sales=1500,sales,RangeSum(Above(Sum(sales),0,2))/2),RangeSum(Above(Sum(sales),0,3))/3)