Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

12 month rolling rangesum in script

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

 CustomerCostSales12 month Cost
01/07/2012A2607,332716,08 
01/08/2012A2470,402621,17 
01/09/2012A3151,343256,64 
01/10/2012A3557,383796,53 
01/11/2012A3293,923545,28 
01/12/2012A6757,867095,38 
01/01/2013A3546,393877,51 
01/02/2013A3985,344089,04 
01/03/2013A3843,583990,89 
01/04/2013A3186,203215,56 
01/05/2013A2838,052964,63 
01/06/2013A2460,422508,17           41 698,21  
01/07/2013A2453,032501,81           41 543,91  
01/08/2013A1969,012017,42           41 042,52  
01/09/2013A2799,552820,12           40 690,72  
01/10/2013A3109,993788,99           40 243,33  
01/11/2013A5307,895437,45           42 257,30  
01/12/2013A5098,755122,93           40 598,19  
01/01/2014A5291,475449,31           42 343,26  
01/02/2014A6350,296374,41           44 708,21  
01/03/2014A3234,943261,26           44 099,56  
01/04/2014A2912,682923,78           43 826,05  
01/05/2014A4322,884469,30           45 310,88  
01/06/2014A2180,732555,43           45 031,19  
01/07/2014A2316,362345,31           44 894,52  
01/04/2012B2927,443093,66 
01/05/2012B1757,941757,94 
01/06/2012B1775,891880,59 
01/07/2012B1610,091701,12 
01/08/2012B1344,771489,80 
01/09/2012B990,371122,33 
01/10/2012B1528,671947,70 
01/11/2012B1478,401815,98 
01/12/2012B3183,183610,94 
01/01/2013B1660,141986,18 
01/02/2013B1617,741704,28 
01/03/2013B1513,941650,48           21 388,55  
01/04/2013B1219,861333,82           19 680,97  
01/05/2013B1242,821259,67           19 165,86  
01/06/2013B833,51862,74           18 223,48  
01/07/2013B688,73765,50           17 302,12  
01/08/2013B593,30708,01           16 550,65  
01/09/2013B1206,921265,18           16 767,20  
01/10/2013B1690,542154,54           16 929,07  
01/11/2013B2408,932457,90           17 859,59  
01/12/2013B2260,082436,08           16 936,49  
01/01/2014B2851,262856,03           18 127,61  

 

Thanks for your help

Labels (2)
1 Solution

Accepted Solutions
Highlighted

Re: 12 month rolling rangesum in script

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;

View solution in original post

2 Replies
Highlighted

Re: 12 month rolling rangesum in script

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;

View solution in original post

Highlighted
Partner
Partner

Re: 12 month rolling rangesum in script

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;