10 Replies Latest reply: Oct 18, 2013 4:55 AM by Tresesco B

# Rolling 3 Months average

Hey Folk,

I have a requirment in which i have to show rolling 3 Months Average in a Bar chart.

Logic: For each month, average should be of previous 3 months including that month.

Example: If Month is Feb-11, then average against Feb in Bar chart should be of Dec-10, Jan-11 and Feb-11.

Likewise it should happen for every month in the Chart in dimension.

Here is the logic which I am trying to implement:

=(sum([Total Doses])

Hoping for solutions....!!!

• ###### Rolling 3 Months average

use

[Total Doses])

or

[Total Doses])

• ###### Re: Rolling 3 Months average

I have tried the formula given by you, but unfortunately its again not serving the purpose.

In the dimension all the months should be visible without any selection.

With that formula, with no selection, no data is there in the chart.

The moment I am clicking on some month, its showing 3 rolling months in the dimension.

For your reference I have attached a dummy app.

• ###### Rolling 3 Months average

hi

use this in your attached app.

• ###### Re: Rolling 3 Months average

Thanks alfasierra,

With this formula I am getting 3 Rolling months of Max(YearMonth) only .

Which means, with no selection I am only getting 3 values in dimension.

But all Values shouls come in Dimension.

• ###### Rolling 3 Months average

hi,

don't understand wath u exactly want .... rolling 3 month for me is see only 3 month...

if u want to see all chanche expression with

is that wath u want?

• ###### Rolling 3 Months average

Or maybe like this?

• ###### Re: Rolling 3 Months average

Hey Just check the attached file.

I hope it would  be clear to you then what I am exactly looking for...!!!

Thanks

• ###### Re: Rolling 3 Months average

In this situation , what i always do is that: add a field named 'Last3MonthsAvg' in the Fact table. This field represents the average sales of last 3 months for the smallest dimensions. In the chart, you sum the values of this field.

i made a test qv file in the attachment and it works.

This is what i do , anybody have other ideas?

thanks

zhou

• ###### Re: Rolling 3 Months average

If you're using month as a dimension and you want 3-month rolling averages then set analysis alone won't suffice. See this document for an explanation and solutions: Calculating rolling n-period totals, averages or other aggregations

• ###### Re: Rolling 3 Months average

Hi,

There would be several ways, one (in the script) is like attached sample. PFA