1 Reply Latest reply: Jan 31, 2012 2:24 PM by Stefan Wühl RSS

    Dynamic accumulation of the last 12 Months.



      Please anyone can help me to solve this problem:


      How I can process a dynamic accumulation of TurnOver for the last 12 months


      •          By using dimensions : Months and Years
      •          But without using accumulation option


      However, I tried to do that with using theoption: accumulation (12 steps Back) and using one dimension:(YEARS_MONTHS_REF) and it works.

      Indeed, through this method I can get the relevant dynamic accumulation for the last 12 months.


      But my boss doesn’t want this kind ofpresentation. He needs a Chart with two dimensions. I put an accent that i needto get the dynamic accumulation for the last 12 Months and not only the value for month.


      The following chart shows the result that Ineed to get


      Sans titre.png

      To be more clear, for example, if we are in October 2011, I should get the accumulation of TurnOver from November 2010 to October 2011.


      It’s really pressing

      Thanks for help

        • Re: Dynamic accumulation of the last 12 Months.
          Stefan Wühl

          I think you could get your accumulated value using rangesum() function together with above() chart inter record function.


          I created some sample data using

          LOAD *, Month(Date) as Month, year(Date) as Year;


          Date(makedate(2011)+recno()-1) as Date,

          ceil(RAND()*100 ) as Value

          AutoGenerate 700;


          Then I created a line chart with dimensions Month and Year to match what I think is shown in your image.


          The expression to accumulate the last 12 months then looks like:

          =aggr(rangesum(above(total sum(Value),0,12)),Year,Month)


          The important part here is the rangesum(above(total sum(Value),0,12)) function that will sum the last 12 Values in a table, for each line (last values means the 12 lines above the current line, thus we use the above() function).

          Since I believe you are showing dimensions in the order Month, Year in your chart, but we want to group by Year, Month, I added an additional advanced aggregation to correct for this.


          You will notice that the acculated line will have a steep slope for the first 12 Month, since we accumulate the last 12 Month and there is not enough data for these month, so Jan 2011 will only accumulate 1 month, Feb 2012 2 months etc.


          See also attached.