Rolling average for weeks that are not in the chart

[#1]

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

[Gysbert_Wassenaar]

See attached qvw

[#3]

Tnx for your input, but it's not totally correct.

The sum of the sales should not change. For example: week 2013/10 should still be 65.

In your case, the sum for that week has changed to 133.

Also I wonder if there is a solution in the chart instead of the script?

[Gysbert_Wassenaar · Accepted solution]

Let's try again. See attached qvw.

It cannot be done it the chart since you don't want the earlier months in the chart. That means that data is not available for calculations.