Rolling 3 Months average

[#1]

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])

+Sum({<YearMonth ={"$(=Date(addmonths(YearMonth, -2),'MMM-YY'))"}>}[Total Doses])

+Sum({<YearMonth ={"$(=(addmonths(Max(YearMonth), -1)))"}>}[Total Doses]))/3

Hoping for solutions....!!!

[SunilChauhan]

use

avg({<YearMonth={">=$(=Date(addmonths(YearMonth, -2),'MMM-YY'))

<=$(=Date(addmonths(YearMonth, 0),'MMM-YY'))"}>}

[Total Doses])

or

avg({<YearMonth={">=$(=Date(addmonths(max(YearMonth), -2),'MMM-YY'))

<=$(=Date(addmonths(max(YearMonth), 0),'MMM-YY'))"}>}

[Total Doses])

[#3]

Thanks Sunil for your reply.

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.

[Anonymous]

hi

use this in your attached app.

Sum({<YearMonth ={">=$(=Date(addmonths(Max(YearMonth), -2), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0),  'MMM-YY'))"} >} Sales)

[#5]

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.

[Anonymous]

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

Sum({<YearMonth ={">=$(=Date(addmonths(Min(YearMonth), -0), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0),  'MMM-YY'))"} >} Sales)

is that wath u want?

[Anonymous]

Or maybe like this?

Sum({<YearMonth ={">=$(=Date(addmonths(Min(YearMonth)), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth), 0),  'MMM-YY'))"} >} Sales)

[#8]

Hey Just check the attached file.

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

Thanks

[#9]

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

[Gysbert_Wassenaar]

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