topic Re: Rolling Total in QlikView

Rolling Total

tracycrown — Wed, 25 Nov 2020 16:16:04 GMT

Dear sir/madam

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

Thank you so much

Tracy

Re: Rolling Total

arpitkharkia — Sat, 20 Apr 2019 15:57:28 GMT

Hi,

PFA, i hope this helps!

Re: Rolling Total

tracycrown — Sun, 21 Apr 2019 02:54:01 GMT

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

Re: Rolling Total

slsaagar — Sun, 21 Apr 2019 07:14:07 GMT

PFA QV File.

hope this helps..

Re: Rolling Total

tracycrown — Sun, 21 Apr 2019 09:06:07 GMT

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

Re: Rolling Total

arpitkharkia — Sun, 21 Apr 2019 09:39:17 GMT

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

Re: Rolling Total

tracycrown — Sun, 21 Apr 2019 14:51:50 GMT

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

Re: Rolling Total

Gysbert_Wassenaar — Sun, 21 Apr 2019 15:55:01 GMT

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

Re: Rolling Total

tracycrown — Wed, 24 Apr 2019 01:44:12 GMT

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