2 Replies Latest reply: Jul 5, 2012 10:11 AM by Francesco Scarlato

# Rolling average - partly Cummulative- in a chart.

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.

Anyone have an idea yet for my problem above ?

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

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