Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Anyone have an idea yet for my problem above ?
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))