Cumulative sum fixed in time

[YoussefBelloum]

Hi everyone,

I'm struggling on keeping my cumulating sums (column stock) fixed on the time axis.

here is a sample of my data set:

Année	Trimestre	Code grande branche	Nombre contrats	Stock
2012	T1	B	2	2
2012	T1	C	12	14
2012	T1	D	5	19
2012	T1	E	20	39
2012	T1	F	143	182
2012	T1	H	1	183
2012	T1	I	3	186
2012	T1		0	186
2012	T2	B	1	187
2012	T2	C	5	192
2012	T2	D	3	195
2012	T2	E	19	214
2012	T2	F	1	215
2012	T2	H	2	217
2012	T2	I	1	218
2012	T2		0	218
2012	T3	C	8	226
2012	T3	D	2	228
2012	T3	E	17	245
2012	T3	F	77	322
2012	T3	H	2	324
2012	T3		0	324
2012	T4	C	1	325
2012	T4	D	4	329
2012	T4	E	18	347
2012	T4	F	30	377
2012	T4	H	1	378
2012	T4	K	2	380
2012	T4		0	380
2013	T1	A	121	501
2013	T1	B	2	503
2013	T1	C	6	509
2013	T1	D	8	517
2013	T1	E	19	536
2013	T1	F	205	741
2013	T1	H	2	743
2013	T1	K	1	744
2013	T1		0	744
2013	T2	B	1	745
2013	T2	C	4	749
2013	T2	D	6	755
2013	T2	E	22	777
2013	T2	F	11	788
2013	T2	H	5	793
2013	T2		0	793
2013	T3	C	2	795
2013	T3	D	3	798
2013	T3	E	17	815
2013	T3	H	1	816
2013	T3		0	816
2013	T4	C	4	820
2013	T4	D	4	824
2013	T4	E	33	857
2013	T4	H	2	859
2013	T4	K	2	861
2013	T4		0	861
2014	T1	B	1	862
2014	T1	C	25	887
2014	T1	D	11	898
2014	T1	E	31	929
2014	T1	F	2	931
2014	T1	H	16	947
2014	T1	I	1	948
2014	T1	K	5	953
2014	T1		0	953
2014	T2	B	1	954
2014	T2	C	5	959
2014	T2	D	4	963
2014	T2	E	32	995
2014	T2	H	3	998
2014	T2	K	2	1000
2014	T2		0	1000
2014	T3	A	1	1001
2014	T3	B	2	1003
2014	T3	C	4	1007
2014	T3	D	2	1009
2014	T3	E	20	1029
2014	T3	H	3	1032
2014	T3	I	2	1034
2014	T3	K	1	1035
2014	T3		0	1035
2014	T4	C	3	1038
2014	T4	E	25	1063
2014	T4	H	2	1065
2014	T4		0	1065
2015	T1	A	1	1066
2015	T1	C	9	1075
2015	T1	D	20	1095
2015	T1	E	13	1108
2015	T1	F	3	1111
2015	T1	H	4	1115
2015	T1	K	1	1116
2015	T1		0	1116
2015	T2	C	7	1123
2015	T2	D	3	1126
2015	T2	E	37	1163
2015	T2	G	1	1164
2015	T2	H	1	1165
2015	T2	K	1	1166
2015	T2		0	1166
2015	T3	B	1	1167
2015	T3	C	8	1175
2015	T3	D	7	1182
2015	T3	E	18	1200
2015	T3	H	2	1202
2015	T3	K	1	1203
2015	T3		0	1203
2015	T4	C	4	1207
2015	T4	D	6	1213
2015	T4	E	22	1235
2015	T4	H	2	1237
2015	T4		0	1237
2016	T1	A	1	1238
2016	T1	B	5	1243
2016	T1	C	7	1250
2016	T1	D	12	1262
2016	T1	E	18	1280
2016	T1	F	1	1281
2016	T1	H	6	1287
2016	T1	I	3	1290
2016	T1	K	3	1293
2016	T1		0	1293
2016	T2	C	5	1298
2016	T2	D	2	1300
2016	T2	E	31	1331
2016	T2	H	1	1332
2016	T2		0	1332
2016	T3	B	1	1333
2016	T3	C	4	1337
2016	T3	D	2	1339
2016	T3	E	25	1364
2016	T3	H	3	1367
2016	T3	K	1	1368
2016	T3		0	1368
2016	T4	C	1	1369
2016	T4	D	7	1376
2016	T4	E	32	1408
2016	T4	H	3	1411
2016	T4	K	2	1413
2016	T4		0	1413
2017	T1	A	1	1414
2017	T1	B	7	1421
2017	T1	C	28	1449
2017	T1	D	21	1470
2017	T1	E	32	1502
2017	T1	F	1	1503
2017	T1	H	15	1518
2017	T1	I	4	1522
2017	T1	K	4	1526
2017	T1		0	1526
2017	T2	C	6	1532
2017	T2	D	7	1539
2017	T2	E	32	1571
2017	T2	H	1	1572
2017	T2	K	1	1573
2017	T2		0	1573
2017	T3	C	4	1577
2017	T3	D	2	1579
2017	T3	E	24	1603
2017	T3	G	1	1604
2017	T3	H	1	1605
2017	T3		0	1605
2017	T4	C	7	1612
2017	T4	D	6	1618
2017	T4	E	21	1639
2017	T4	H	5	1644
2017	T4		0	1644
2018	T1	A	1	1645
2018	T1	B	31	1676
2018	T1	C	15	1691
2018	T1	D	18	1709
2018	T1	E	28	1737
2018	T1	H	8	1745
2018	T1	I	1	1746
2018	T1	K	5	1751
2018	T1		0	1751
2018	T2	B	2	1753
2018	T2	C	8	1761
2018	T2	D	7	1768
2018	T2	E	33	1801
2018	T2	H	5	1806
2018	T2		0	1806
2018	T3	B	2	1808
2018	T3	C	9	1817
2018	T3	D	8	1825
2018	T3	E	17	1842
2018	T3	F	1	1843
2018	T3	H	5	1848
2018	T3	I	1	1849
2018	T3		0	1849
2018	T4	B	1	1850
2018	T4	C	2	1852
2018	T4	D	3	1855
2018	T4	E	29	1884
2018	T4	H	1	1885
2018	T4		0	1885
2019	T1	A	1	1886
2019	T1	B	6	1892
2019	T1	C	31	1923
2019	T1	D	31	1954
2019	T1	E	102	2056
2019	T1	F	2	2058
2019	T1	H	14	2072
2019	T1	I	1	2073
2019	T1	K	9	2082
2019	T1		0	2082
2019	T2	B	1	2083
2019	T2	C	15	2098
2019	T2	D	10	2108
2019	T2	E	124	2232
2019	T2	H	4	2236
2019	T2	I	1	2237
2019	T2	K	2	2239
2019	T2		0	2239
2019	T3	B	2	2241
2019	T3	C	14	2255
2019	T3	D	5	2260
2019	T3	E	102	2362
2019	T3	H	4	2366
2019	T3		0	2366
2019	T4	B	1	2367
2019	T4	C	15	2382
2019	T4	D	11	2393
2019	T4	E	91	2484
2019	T4	H	1	2485
2019	T4		0	2485
2020	T1	B	3	2488
2020	T1	C	18	2506
2020	T1	D	17	2523
2020	T1	E	116	2639
2020	T1	H	7	2646
2020	T1	I	32	2678
2020	T1	K	1	2679
2020	T1		0	2679
2020	T2	B	1	2680
2020	T2	C	6	2686
2020	T2	D	7	2693
2020	T2	E	29	2722
2020	T2	H	2	2724
2020	T2	I	2	2726

I've calculated the cumulating sum using RangeSum and peek on the script and using the expression below on the front end:

=RangeSum(Above(total sum(NOMBRE_CONTRAT),0,RowNo(TOTAL)))

It works fine until I make selections on the time axis (Year or quarter for example), using fields Année and Trimestre.

What are the modifications to be made on the expression to make the cumulations fixed, to get everytime the same values on the column Stock.

Example, when I choose year 2020, i need this:

2020	T1	B	3	2488
2020	T1	C	18	2506
2020	T1	D	17	2523
2020	T1	E	116	2639
2020	T1	H	7	2646
2020	T1	I	32	2678
2020	T1	K	1	2679
2020	T1		0	2679
2020	T2	B	1	2680
2020	T2	C	6	2686
2020	T2	D	7	2693
2020	T2	E	29	2722
2020	T2	H	2	2724
2020	T2	I	2	2726
2020	T2		0	2726
2020	T4		0	2726

Thank you !

Youssef

[Kushal_Chawda]

@YoussefBelloum  modify your expression little bit as below. Assuming you have two dimensions as highlighted below in table. If you have more dimension then add as well in set & aggr as highlighted

=sum(aggr(RangeSum(Above(total sum({<Année,Trimestre>}NOMBRE_CONTRAT),0,RowNo(TOTAL))),Année,Trimestre))*avg(1)

[YoussefBelloum]

Hi @Kushal_Chawda 

the extra aggr seems to fix the values when I select a year or a quarter BUT the values are not correct anymore..

here is what i get with it:

Année Trimestre Nombre contrats Stock
2012 T1 186 2139
2012 T2 32 1897
2012 T3 106 2245
2012 T4 56 1953
2013 T1 364 2703
2013 T2 49 2294
2013 T3 23 2726
2013 T4 45 2339
2014 T1 92 1830
2014 T2 47 1708
2014 T3 35 1865
2014 T4 30 1738
2015 T1 51 1624
2015 T2 50 1539
2015 T3 37 1661
2015 T4 34 1573
2016 T1 56 381
2016 T2 39 280
2016 T3 36 417
2016 T4 45 325
2017 T1 113 1216
2017 T2 47 1064
2017 T3 32 1248
2017 T4 39 1103
2018 T1 107 1446
2018 T2 55 1303
2018 T3 43 1489
2018 T4 36 1339
2019 T1 197 890
2019 T2 157 574
2019 T3 127 1017
2019 T4 119 693
2020 T1 194 241
2020 T2 47 47
2020 T4 0 47

[Kushal_Chawda]

@YoussefBelloum may be because aggr function broke the sorting of your data. try below. I am assuming the format of Année is number and Trimestre is text. Change the parameter as per your data values. Look at the parameters here

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr...

=sum(aggr(RangeSum(Above(total sum({<Année,Trimestre>}NOMBRE_CONTRAT),0,RowNo(TOTAL))),

(Année(NUMERIC,ASCENDING)), (Trimestre(TEXT,ASCENDING))))*avg(1)

[sunny_talwar]

@Kushal_Chawda I don't think Avg(1) is needed because Sum() or Only() after Aggr() should take care of it

Aggr(
  RangeSum(Above(TOTAL Sum({<Année, Trimestre>} NOMBRE_CONTRAT), 0, RowNo(TOTAL)))
, (Année, (NUMERIC)), (Trimestre, (TEXT)))

[Kushal_Chawda]

@sunny_talwar  yes. not needed. In some scenarios it requires so keep it. Doesn't have  any harm to keep it actually for safer side.

[YoussefBelloum]

@Kushal_Chawda thanks for your reply, but the syntax is incorrect..

this one works fine (It was missing the 3rd dimension):

=RangeSum(Above(total sum({<Année,Trimestre,[Code grande branche]>}NOMBRE_CONTRAT),0,RowNo(TOTAL)))*avg(1)

one last question please. I find out that my rangesum on the script acts like a simple sum, those numbers I want to get them from the script to use a simple sum() on the front end. is it possible ?

[YoussefBelloum]

Hi @sunny_talwar  thanks for your reply, but I get nulls because the syntax is incorrect

[Kushal_Chawda · Accepted solution]

@YoussefBelloum  Yes. It is possible via script as well. But you can change your expression below and try because frond end expressions will have more flexibility if you want to select any other dimensions apart from what you have in table

=sum(aggr(RangeSum(Above(total sum({<Année,Trimestre,[Code grande branche]>}NOMBRE_CONTRAT),0,RowNo(TOTAL))), (Année,NUMERIC), (Trimestre,TEXT),[Code grande branche]))*avg(1)

[YoussefBelloum]

@Kushal_Chawda the last one is working 🙂 But on the last part: (Année(NUMERIC)), (Trimestre(TEXT)) the syntax seems to be incorrrect like I said to @sunny_talwar 

I changed it to : (Année,NUMERIC),(Trimestre,TEXT)

Is it a syntax that has changed with the versions of qlikview or what  ?