Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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 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)
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
@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
=sum(aggr(RangeSum(Above(total sum({<Année,Trimestre>}NOMBRE_CONTRAT),0,RowNo(TOTAL))),
(Année(NUMERIC,ASCENDING)), (Trimestre(TEXT,ASCENDING))))*avg(1)
@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)))
@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.
@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 ?
Hi @sunny_talwar thanks for your reply, but I get nulls because the syntax is incorrect
@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)
@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 ?