3 Replies Latest reply: Mar 11, 2013 1:14 PM by Gysbert Wassenaar RSS

    Rolling average for weeks that are not in the chart

      Hi everybody,

       

      I have the following problem:

       

      The table is as following:

       

      week, sales

      2012/51, 66

      2012/52, 55

      2013/01, 3

      2013/02, 9

      2013/03, 29

      2013/04, 15

      2013/05, 16

      2013/06, 38

      2013/07, 7

      2013/08, 16

      2013/09, 45

      2013/10, 65

       

       

      I want to make a Line/Bar chart where i see the value of sales per week starting weekname(today) and I want a Line within the bar chart for the average sales per week over 4 weeks. For example:

       

      In my graph weekname 2013/03 should calculate the average
      for that week as follows:

       

      Weekname 2012/52 + 2013/01 + 2013/02 + 2013/03 = (55 + 3 + 9
      + 29) / 4 = 24

       

      Weekname 2013/04 should be: (3 + 9 + 29 + 15) / 4 = 14.

       

      These 24 and 14 should be added to the graph (as a line).
      The graph should start this week at 2013/03 and the next week at 2013/04.

       

      I have included an example file with the solutions i tried but didnt work.

       

      Any help is appreciated.

       

      Thanks,

      SY