Rolling Total

[tracycrown]

Dear sir/madam

Kindly help to advise how to  write a script to compute rolling total sales.

Thank you so much

Tracy

[arpitkharkia]

Hi,

PFA, i hope this helps!

[tracycrown]

Dear Arpitkharkia

Many thanks for your quick response.

I have select only year 2014 but the chart still show all data in 2013, kindly advise asap.

Thank you

Tracy

[slsaagar]

PFA QV File.

hope this helps..

[tracycrown]

Dear Slaagar

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).

Thank you

Tracy

[arpitkharkia]

Hi Tracy,

Sorry for the delay in response. PFA the updated qvw. This may not be the best solution but its giving the desired result.

Thanks!

Arpit

[tracycrown]

Dear Arpit

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

Tracy 

[Gysbert_Wassenaar]

See this document for solutions: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...

[tracycrown]

Dear Gysbert

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
		Gysbert	Tracy
1	6	6
2	4	10
3	7	17	17
4	3	14	14
5	4	14	14
6	9	16	16
7	5	18	18
8	7	21	21
9	8	20	20
10	6	21	21
11	9	23	23
12	7	22	22

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?.

Thank You

Tracy