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

    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!