2 Replies Latest reply: Jan 7, 2014 11:30 AM by Richard McMullan RSS

    Creating a trailing 52 week chart

      Hi

       

      I'm trying to create a chart which shows the trailing 52 week total for a variable (in this example Sales).  Basically, on the chart below, the value for weekname 2004/01 is the sum of that week's sales plus the previous 51 weeks' sales, for 2013/52 it is the sum of that week and the previous 51 weeks sales.  I also want to restrict the view to the last 26 weeknames.  The attached spreadsheet gives an example of the data and the graph I'm trying to create.

      t52w chart.PNG.png

       

      I have tried using the formula:   Rangesum(Above(Total sum(Sales),0,52))  which works fine until I apply the dimension limit of using the first 26 values!  I've mucked around with various options, but have now hit a brick wall.

       

      All ideas & help greatly appreciated!

       

      thanks

      Richard