Sorry to say that this is not what I want, thanks for your help.
Please note that the rolling total column in the Test-1.xls is just an example to illustrate the formula and correct answer of Rolling Total. In other word, you should use Sales data to derive Rolling Total in the script to plot the chart.
The script advised by Arpitkharkia is CORRECT but unforfortually it shows Rolling Totals for "ALL" dates from year 2013 to 2014 and unable to provide Rolling Totals for selected date range (eg. only 2014).
This is not the correct answer.
If I select date range from Dec 31, 2013 to Dec 31, 2014, your 2nd qvw cannot get $7,310 on Dec 31, 2013 (same as my excel worksheet in Test-1.xls).
Your 1st qvw gave the correct answer $7310 but cannot select data range, kindly help asap.
Thank you very much for sharing with me such a comprehensive discussion on Rolling Total, great effort has been put in the research to benefit the QV community.
It seems to me that your definition and formula of Rolling 3 months Total is different from mine (Tracy) as follows :
1. Nothing shown in row 1 & 2 as Month < 3
2. Month 3 = 17 = Total of rows from 1 to 3 (6+4+7)
3. Month 4 = 14 = Total of rows from 2 to 4 (4+7+3)
|Month||Amount||Rolling 3||Rolling 3|
Both answers are the same, kindy advise what script should I change to eliminate data in rows 1 & 2 and not sure whether the answers will be the same for Rolling Total 12 Months?.