Rolling average - partly Cummulative- in a chart.

Wed, 04 Jul 2012 11:07:23 GMT

Hi. I'm having trouble creating a chart which has a rolling average in it.

The graph only shows the selected year.

I have a monthnumber ( rowno() ) assigned to my datetable. This field is MonthNumber.

The formula:

---------------------

sum({ Year=, Quarter=, Month=, MonthNumber={">= $(=Max(MonthNumber)-11) <= $(=Max(MonthNumber)-0)"}>} Amount)/

sum({ Year=, Quarter=, Month=, MonthNumber={">= $(=Max(MonthNumber)-0)"}>} OtherAmount)

---------------------

Now, I got this formula to work to diplay the current month only in a table without a dimension.

But not rolling in a graph.

The working formula to only show this month is:

---------------------

// LEDGER LAST 12 MONTHS

sum({< Year=, Quarter=, Month=, MonthNumber={">= $(=Max(MonthNumber)-11) <= $(=Max(MonthNumber))"}>} GL_Amount_Credit-GL_Amount_Debit)

// Capital Employed

(

sum( {< Year=, Quarter=, Month=, MonthNumber= {$(#=Max(MonthNumber)-0)} >} GL_Amount_Debit-GL_Amount_Credit)

+sum( {<Year=, Quarter=, Month=, MonthNumber= {$(#=Max(MonthNumber)-0)} >} Begsal_Debet-Begsal_Credit)

)

---------------------

Can anyone provide me with the correct formula for the graph?

Asuming a dimension YearMonth (201001, 201002).

Thanks!

Re: Rolling average - partly Cummulative- in a chart.

Thu, 05 Jul 2012 13:25:46 GMT

Anyone have an idea yet for my problem above ?

Thu, 05 Jul 2012 14:11:02 GMT

I did not understand what you want, could you post an example of the table with some expected values, please?

The moving average can be obtained with the function and RangeAvg and Above

e.g. if you have the formula sum(Field), the moving average of previous 4 period can be calculated with

RangeAvg(Above(sum(Field),1,3), sum(Field))