[SOLVED] Cumulative sum with condition

Cumulative sum with condition

I wish to derive cumulative column based on initially adding the daily amount to the Value, then subsequently adding the daily amount to the resulting figure.

Could you please help, thanks.

Date	Type	Value	Rate	Cummulative
29/04/2022	A	128.61	32.00	256.61
28/04/2022	A	128.61	32.00	224.61
27/04/2022	A	128.61	32.00	192.61
26/04/2022	A	128.61	32.00	160.61

Solution

Have a look at the example script below. Once reloaded the CumulativeData table will contain new column Cumulative which will be the result.

The "magic" is happening in the following expression:

if(RecNo() = 1, 
     Value + Rate, 
     peek(Cumulative) + Rate
) as Cumulative

In the expression we are saying:

if the record number is 1 (first row of the table) then sum Value and Rate values. This is our "base" and we'll accumulate to this value
for the next rows get the above (previous row) value of Cumulative column (peek(Cumulative)) and add the current row Rate value

More about peek function can be found at the documentation page

Example script:

RawData:
Load * Inline [
Date      , Type,   Value , Rate
29/04/2022, A   ,   128.61, 32.00
28/04/2022, A   ,   128.61, 32.00
27/04/2022, A   ,   128.61, 32.00
26/04/2022, A   ,   128.61, 32.00
];

// Dont foget to order the table in ascending order
CumulativeData:
Load 
  *,
  if(RecNo() = 1, 
       Value + Rate, 
       peek(Cumulative) + Rate
  ) as Cumulative
Resident
  RawData
Order By
  Date ASC
;

Drop Table RawData;

Result table: