topic Re: Cumulative Sum in App Development

Cumulative Sum

americanetsMD — Tue, 07 Nov 2023 13:31:06 GMT

Hi everyone,

consider the next table:

ID	CheckNr	Year	Period	Value	Cumulative EXPECTED

3002	3002295481	2022	01.2022	39.531,00	39.531,00
3002	3002131001	2022	01.2022	0,00	39.531,00
3002	3002295481	2022	02.2022	17.729,00	57.260,00
3002	3002131001	2022	03.2022	14.910,00	72.170,00
3002	3002280650	2022	04.2022	38.224,00	110.394,00
3002	3002280650	2022	05.2022	22.042,00	132.436,00
3002	3002280650	2022	06.2022	-7.395,00	125.041,00
3002	3002295481	2022	07.2022	-18.807,00	106.234,00
3002	3002131001	2022	08.2022	33.148,00	139.382,00
3002	3002131001	2022	09.2022	-18.827,00	120.555,00
3002	3002124331	2022	10.2022	11.340,00	131.895,00
3002	3002223674	2022	11.2022	-9.730,00	122.165,00
3002	3002305918	2022	12.2022	-7.425,00	114.740,00
3002	3002124331	2022	12.2022	-25.964,00	88.776,00
3002	3002124251	2023	02.2023	2.023,00	90.799,00
3002	3002131001	2023	03.2023	86.068,00	176.867,00
3002	3002131001	2023	04.2023	-136.292,00	40.575,00
3002	3002351693	2023	05.2023	3.207,00	43.782,00
3002	3002131001	2023	06.2023	102.385,00	146.167,00
3002	3002131001	2023	07.2023	-105.491,00	40.676,00
3002	keine Zuordnung	2023	08.2023	88.776,00	129.452,00
3002	3002124331	2023	08.2023	14.624,00	144.076,00
3002	3002223674	2023	08.2023	9.730,00	153.806,00
3002	3002305918	2023	08.2023	7.425,00	161.231,00
3002	3002280650	2023	08.2023	-22.965,00	138.266,00
3002	3002131001	2023	08.2023	-29.231,00	109.035,00
3002	3002295481	2023	08.2023	-38.453,00	70.582,00
3002	3002280650	2023	09.2023	19.170,00	89.752,00

For this table i have to calculate the cumulative amount for each ID, no matter the period, as you can see in the column "Cumulative EXPECTED".

I have to create a Pivot table based on these columns (without column "Value").

Can anyone please suggest a way i can do this. 😞

Re: Cumulative Sum

americanetsMD — Tue, 07 Nov 2023 13:37:44 GMT

or how to get a cumulative sum per year, where amount for january every year = amount january + total amount last year.

Re: Cumulative Sum

vincent_ardiet_ — Tue, 07 Nov 2023 14:01:58 GMT

Something like this:
RangeSum(Above(TOTAL Sum(Value),1,RowNo(TOTAL)),Sum(Value))

Re: Cumulative Sum

americanetsMD — Tue, 07 Nov 2023 14:20:36 GMT

Hi Vincent,

This works fine in a straight table. However, i cannot use this formula in the pivot table. This is why i am hoping there is a way to do this in the script maybe, so that i have a column with all cumulated amount.

p.s i have also tried to get the result by creating an As-Of Table by Period, but the number is reseted every year, and i need that amount from period 12 last year. 😞

Re: Cumulative Sum

vincent_ardiet_ — Tue, 07 Nov 2023 14:32:30 GMT

Ok, in the script do something like this (your source table is [SourceTable]):

TargetTable:
Load *,RangeSum(Value,Peek(Cumulative)) as Cumulative Resident SourceTable
order by Period,CheckNr;

drop Table SourceTable;

Re: Cumulative Sum

americanetsMD — Tue, 07 Nov 2023 15:06:40 GMT

I have tried you code, but i get some weird numbers... i believe that the reasons are that there are multiple IDs in the table, and the Order By also does not work somehow properly.

I mean, i have filtered the table to one ID and tried the Order By different fields and also made it ASC, and i am still getting incorect values.

Re: Cumulative Sum

vincent_ardiet_ — Tue, 07 Nov 2023 15:19:56 GMT

Maybe this is because your field Period is not seen as a date but as a number.
For example, this one is working fine on my side, but I have converted Period initially.

SourceTable:
Load ID,CheckNr,Year,MakeDate(Right(Period,4),Left(Period,2)) as Period,Value Inline
[ID,CheckNr,Year,Period,Value
3002,3002295481, 2022,01.2022,39531
3002,3002131001, 2022,01.2022,0
3002,3002295481, 2022,02.2022,17729
3002,3002131001, 2022,03.2022,14910
3002,3002280650, 2022,04.2022,38224
3002,3002280650, 2022,05.2022,22042
3002,3002280650, 2022,06.2022,-7395
3002,3002295481, 2022,07.2022,-18807
3002,3002131001, 2022,08.2022,33148
3002,3002131001, 2022,09.2022,-18827
3002,3002124331, 2022,10.2022,11340
3002,3002223674, 2022,11.2022,-9730
3002,3002305918, 2022,12.2022,-7425
3002,3002124331, 2022,12.2022,-25964
3002,3002124251, 2023,02.2023,2023
3002,3002131001, 2023,03.2023,86068
3002,3002131001, 2023,04.2023,-136292
3002,3002351693, 2023,05.2023,3207
3002,3002131001, 2023,06.2023,102385
3002,3002131001, 2023,07.2023,-105491
3002,keine Zuordnung,2023,08.2023,88776
3002,3002124331, 2023,08.2023,14624
3002,3002223674, 2023,08.2023,9730
3002,3002305918, 2023,08.2023,7425
3002,3002280650, 2023,08.2023,-22965
3002,3002131001, 2023,08.2023,-29231
3002,3002295481, 2023,08.2023,-38453
3002,3002280650, 2023,09.2023,19170];

TargetTable:
Load *,RangeSum(Value,Peek(Cumulative)) as Cumulative Resident SourceTable
order by Period,CheckNr;

drop Table SourceTable;

Re: Cumulative Sum

americanetsMD — Wed, 08 Nov 2023 10:19:53 GMT

Hi Vincent, this code seems to work only when there is one ID. But i have Many ID, that can have the same checkNr.

I was wondering if it's possible to somehow sum cumulative for the whole year, and store the value into january next year. In this case, cumulate sum for year 2022 must be = 88.776,00. This value should be stored as new value for Periode 01.2023. Then i could comfortably use the As- Of Table in order to get the cumulative sums for each year in the Pivot Table.

Re: Cumulative Sum

americanetsMD — Wed, 08 Nov 2023 10:22:42 GMT

In the end it would look like:

3002	3002124331	2022	12.2022	-25.964,00	88.776,00
3002	sumLastYear	2023	01.2023	88.776,00	88.776,00
3002	3002124251	2023	02.2023	2.023,00	90.799,00

Re: Cumulative Sum

vincent_ardiet_ — Wed, 08 Nov 2023 10:52:45 GMT

Sorry I missed the "for each ID", this should be better, and I have added a YearlyCumulative field:

TargetTable:
Load
*
,If(Peek(ID)=ID,RangeSum(Value,Peek(TotalCumulative)),Value) as TotalCumulative
,If(Peek(Year)=Year and Peek(ID)=ID,RangeSum(Value,Peek(YearlyCumulative)),Value) as YearlyCumulative
Resident SourceTable
order by ID,Period,CheckNr;

Re: Cumulative Sum

americanetsMD — Wed, 15 Nov 2023 14:58:43 GMT

Thank you very much Vincent. Managed to get the results. 🙂