12 month rolling rangesum in script

[reivax31]

Hi Qlikers,

I have the following table and in the last column I'm showing the expected result. What It need is for each customers a cumulative sum of the costs (the first 11 month no calculation is performed). 

	Customer	Cost	Sales	12 month Cost
01/07/2012	A	2607,33	2716,08
01/08/2012	A	2470,40	2621,17
01/09/2012	A	3151,34	3256,64
01/10/2012	A	3557,38	3796,53
01/11/2012	A	3293,92	3545,28
01/12/2012	A	6757,86	7095,38
01/01/2013	A	3546,39	3877,51
01/02/2013	A	3985,34	4089,04
01/03/2013	A	3843,58	3990,89
01/04/2013	A	3186,20	3215,56
01/05/2013	A	2838,05	2964,63
01/06/2013	A	2460,42	2508,17	41 698,21
01/07/2013	A	2453,03	2501,81	41 543,91
01/08/2013	A	1969,01	2017,42	41 042,52
01/09/2013	A	2799,55	2820,12	40 690,72
01/10/2013	A	3109,99	3788,99	40 243,33
01/11/2013	A	5307,89	5437,45	42 257,30
01/12/2013	A	5098,75	5122,93	40 598,19
01/01/2014	A	5291,47	5449,31	42 343,26
01/02/2014	A	6350,29	6374,41	44 708,21
01/03/2014	A	3234,94	3261,26	44 099,56
01/04/2014	A	2912,68	2923,78	43 826,05
01/05/2014	A	4322,88	4469,30	45 310,88
01/06/2014	A	2180,73	2555,43	45 031,19
01/07/2014	A	2316,36	2345,31	44 894,52
01/04/2012	B	2927,44	3093,66
01/05/2012	B	1757,94	1757,94
01/06/2012	B	1775,89	1880,59
01/07/2012	B	1610,09	1701,12
01/08/2012	B	1344,77	1489,80
01/09/2012	B	990,37	1122,33
01/10/2012	B	1528,67	1947,70
01/11/2012	B	1478,40	1815,98
01/12/2012	B	3183,18	3610,94
01/01/2013	B	1660,14	1986,18
01/02/2013	B	1617,74	1704,28
01/03/2013	B	1513,94	1650,48	21 388,55
01/04/2013	B	1219,86	1333,82	19 680,97
01/05/2013	B	1242,82	1259,67	19 165,86
01/06/2013	B	833,51	862,74	18 223,48
01/07/2013	B	688,73	765,50	17 302,12
01/08/2013	B	593,30	708,01	16 550,65
01/09/2013	B	1206,92	1265,18	16 767,20
01/10/2013	B	1690,54	2154,54	16 929,07
01/11/2013	B	2408,93	2457,90	17 859,59
01/12/2013	B	2260,08	2436,08	16 936,49
01/01/2014	B	2851,26	2856,03	18 127,61

Thanks for your help

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    Date, Customer, Cost, Sales
    01/07/2012, A, 2607.33, 2716.08
    01/08/2012, A, 2470.40, 2621.17
    01/09/2012, A, 3151.34, 3256.64
    01/10/2012, A, 3557.38, 3796.53
    01/11/2012, A, 3293.92, 3545.28
    01/12/2012, A, 6757.86, 7095.38
    01/01/2013, A, 3546.39, 3877.51
    01/02/2013, A, 3985.34, 4089.04
    01/03/2013, A, 3843.58, 3990.89
    01/04/2013, A, 3186.20, 3215.56
    01/05/2013, A, 2838.05, 2964.63
    01/06/2013, A, 2460.42, 2508.17
    01/07/2013, A, 2453.03, 2501.81
    01/08/2013, A, 1969.01, 2017.42
    01/09/2013, A, 2799.55, 2820.12
    01/10/2013, A, 3109.99, 3788.99
    01/11/2013, A, 5307.89, 5437.45
    01/12/2013, A, 5098.75, 5122.93
    01/01/2014, A, 5291.47, 5449.31
    01/02/2014, A, 6350.29, 6374.41
    01/03/2014, A, 3234.94, 3261.26
    01/04/2014, A, 2912.68, 2923.78
    01/05/2014, A, 4322.88, 4469.30
    01/06/2014, A, 2180.73, 2555.43
    01/07/2014, A, 2316.36, 2345.31
    01/04/2012, B, 2927.44, 3093.66
    01/05/2012, B, 1757.94, 1757.94
    01/06/2012, B, 1775.89, 1880.59
    01/07/2012, B, 1610.09, 1701.12
    01/08/2012, B, 1344.77, 1489.80
    01/09/2012, B, 990.37, 1122.33
    01/10/2012, B, 1528.67, 1947.70
    01/11/2012, B, 1478.40, 1815.98
    01/12/2012, B, 3183.18, 3610.94
    01/01/2013, B, 1660.14, 1986.18
    01/02/2013, B, 1617.74, 1704.28
    01/03/2013, B, 1513.94, 1650.48
    01/04/2013, B, 1219.86, 1333.82
    01/05/2013, B, 1242.82, 1259.67
    01/06/2013, B, 833.51, 862.74
    01/07/2013, B, 688.73, 765.50
    01/08/2013, B, 593.30, 708.01
    01/09/2013, B, 1206.92, 1265.18
    01/10/2013, B, 1690.54, 2154.54
    01/11/2013, B, 2408.93, 2457.90
    01/12/2013, B, 2260.08, 2436.08
    01/01/2014, B, 2851.26, 2856.03
];

FinalTable:
LOAD *,
	 If(Customer = Previous(Customer), RangeSum(Peek('Count'), 1), 1) as Count,
	 If(Customer = Previous(Customer) and Peek('Count') >= 11, RangeSum(Cost, Peek('Cost'), Peek('Cost', -2), Peek('Cost', -3), Peek('Cost', -4), Peek('Cost', -5), Peek('Cost', -6),
	 																	Peek('Cost', -7), Peek('Cost', -8), Peek('Cost', -9), Peek('Cost', -10), Peek('Cost', -11))) as [12 month cost]
Resident Table
Order By Customer, Date;

DROP Table Table;
DROP Field Count;

[reivax31]

I had an other solution that worked but not very clean. I prefer yours

DATA_PROD:

Load
*,
if(CUMUL_FLAG>=12,Cost
+ peek ('Cost',-1)
+ peek ('Cost',-2)
+ peek ('Cost',-3)
+ peek ('Cost',-4)
+ peek ('Cost',-5)
+ peek ('Cost',-6)
+ peek ('Cost',-7)
+ peek ('Cost',-8)
+ peek ('Cost',-9)
+ peek ('Cost',-10)
+ peek ('Cost',-11)
,0) as 12_month_cost
;

LOAD
*,
if(Peek('Customer')<>Customer,
FLAG, FLAG+ peek ('CUMUL_FLAG')) AS CUMUL_FLAG
;

LOAD
*,
if(Cost>0, 1 ,0) AS FLAG 
resident DATA_PROD_TEMP
order by Customer,MEASURE_DATE ;

drop table DATA_PROD_TEMP;

[awp2020]

Hi,

could you explain me how to modify the script to get a cumulative value when it's less than 12 months like below  ? :

thank you for your help 

	Customer	Cost	Sales	12 month Cost
01/07/2012	A	2607,3	2716,1	2607,3
01/08/2012	A	2470,4	2621,2	5077,7
01/09/2012	A	3151,3	3256,6	8229,1
01/10/2012	A	3557,4	3796,5	11786
01/11/2012	A	3293,9	3545,3	15080
01/12/2012	A	6757,9	7095,4	21838
01/01/2013	A	3546,4	3877,5	25385
01/02/2013	A	3985,3	4089	29370
01/03/2013	A	3843,6	3990,9	33214
01/04/2013	A	3186,2	3215,6	36400
01/05/2013	A	2838,1	2964,6	39238
01/06/2013	A	2460,4	2508,2	41698

[awp2020]

Hi, no one to help me ?

[awp2020]

Hello,

I found it !

Table:
LOAD * INLINE [
Date, Customer, Cost, Sales
01/07/2012, A, 2607.33, 2716.08
01/08/2012, A, 2470.40, 2621.17
01/09/2012, A, 3151.34, 3256.64
01/10/2012, A, 3557.38, 3796.53
01/11/2012, A, 3293.92, 3545.28
01/12/2012, A, 6757.86, 7095.38
01/01/2013, A, 3546.39, 3877.51
01/02/2013, A, 3985.34, 4089.04
01/03/2013, A, 3843.58, 3990.89
01/04/2013, A, 3186.20, 3215.56
01/05/2013, A, 2838.05, 2964.63
01/06/2013, A, 2460.42, 2508.17
01/07/2013, A, 2453.03, 2501.81
01/08/2013, A, 1969.01, 2017.42
01/09/2013, A, 2799.55, 2820.12
01/10/2013, A, 3109.99, 3788.99
01/11/2013, A, 5307.89, 5437.45
01/12/2013, A, 5098.75, 5122.93
01/01/2014, A, 5291.47, 5449.31
01/02/2014, A, 6350.29, 6374.41
01/03/2014, A, 3234.94, 3261.26
01/04/2014, A, 2912.68, 2923.78
01/05/2014, A, 4322.88, 4469.30
01/06/2014, A, 2180.73, 2555.43
01/07/2014, A, 2316.36, 2345.31
01/04/2012, B, 2927.44, 3093.66
01/05/2012, B, 1757.94, 1757.94
01/06/2012, B, 1775.89, 1880.59
01/07/2012, B, 1610.09, 1701.12
01/08/2012, B, 1344.77, 1489.80
01/09/2012, B, 990.37, 1122.33
01/10/2012, B, 1528.67, 1947.70
01/11/2012, B, 1478.40, 1815.98
01/12/2012, B, 3183.18, 3610.94
01/01/2013, B, 1660.14, 1986.18
01/02/2013, B, 1617.74, 1704.28
01/03/2013, B, 1513.94, 1650.48
01/04/2013, B, 1219.86, 1333.82
01/05/2013, B, 1242.82, 1259.67
01/06/2013, B, 833.51, 862.74
01/07/2013, B, 688.73, 765.50
01/08/2013, B, 593.30, 708.01
01/09/2013, B, 1206.92, 1265.18
01/10/2013, B, 1690.54, 2154.54
01/11/2013, B, 2408.93, 2457.90
01/12/2013, B, 2260.08, 2436.08
01/01/2014, B, 2851.26, 2856.03
];

DATA_1:
LOAD *,
year(Date) as Year,
month(Date) as Month

Resident Table;
DROP Table Table;

FinalTable:
LOAD *,


If(Customer = Previous(Customer), RangeSum(Peek('Count'), 1), 1) as Count,
If(Customer = Previous(Customer) and Peek('Count') >= 11, RangeSum(Cost, Peek('Cost'), Peek('Cost', -2), Peek('Cost', -3),
Peek('Cost', -4), Peek('Cost', -5), Peek('Cost', -6),Peek('Cost', -7), Peek('Cost', -8), Peek('Cost', -9), Peek('Cost', -10), Peek('Cost', -11))) as [12 month cost],

If(Peek('Year')=Year, RangeSum(Peek('YTD_COST'), Cost), Cost) as YTD_COST

Resident DATA_1
Order By Customer,Date,Year,Month;

Drop table DATA_1;
DROP Field Count;