How to display a moving average in a rolling 12 month?

[tabletuner]

Hi all,

I am trying to build a chart with the dimension Year-Month and an expression that calculates the moving average sales over the past 36 months. Another demand is that the chart will only show the rolling past 12 months and the field 'Year' is always max 1 selected.

I built the following expression that works partially:

rangeavg(above(sum(
{$<
Year={'*'},
Month={'*'}
>}
Sales),0,36)

This chart displays the moving average but not for the 12 months but for 36 months! My user must always select the field Year and the chart must then display only 12 values. If i leave out the Year field in the set analysis, the moving average is recalculated for only 12 months when the user select only 1 year. However it should still be calculated over 36 months...

I included a picture to make the problem easier to understand. The same values in the picture should be displayed for 12 months...

Hope someone can help me out.

Regards,

Tjeerd

[tresesco]

probably this could be an option: in the presentation tab limit the display to 12. now you have to sort your dimension in descending order AND probably now your above function would have to be replaced by below().

Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4

and yes, make a variable to hold maximum date (vmaxDate=Max(dateField)). put this variable in the set analysis as DateField<=vMaxDate. then your Year selection would work for upper bound.

give a try, (not quite sure).

Regards, tresesco

[tabletuner]

Hi Tresesco,

I think part of your answer isnt properly displayed... however thanks for your inspiration anyway!

Your option to limit the presentation tab to 12 values affects the rangeavg() function directly because that function only uses the displayed values for calculations. In my case 12 values instead of 36.

I've included an example to make things more easy to understand.

Regards,

Tjeerd

[Oleg_Troyansky]

The best way to calculate Moving Averages is to maintain an additional data table, with so called "As Of" dates and "Rolling 12 Months" flags. THis solution has been described many times in this forum - search for "as of dates"...

In a nutshell, you add another table linked to your transactional Date (or Month), and keep there those "As of" months that the transactional Date falls into their "Rolling 12 months" time frame. Then, you use the "As Of Date" as your chart dimension, and naturally all the related transactional dates will be included in the calculation.

[tresesco]

"Your option to limit the presentation tab to 12 values affects the rangeavg() function directly because that function only uses the displayed values for calculations. In my case 12 values instead of 36. "

to get the above resolved : check the allow horizontal scroll bar option, and to hide from the user use a text object over it. Now your values would be calculated based on 36 months (though they are not in front of the user, they are calculated at the backend) .........i hope so.

and yes you can try it as Oleg has suggested (i did not have much time to follow it), if get to solution, please don't forget to post the sample application here.

Regards, tresesco