Compute a cumulative sum in QLIK load script

[annick]

Hello,

I would like to compute a cumulative sum of sales per date in my load script:

sales:
LOAD
* Inline [
SalesDate, NbSales
'2023-01-10', 8
'2023-02-01', 4
'2023-03-15', 5
'2023-04-20', 9
'2023-05-02', 4
'2023-06-23', 2
'2023-07-12', 20
];

i.e. from this table:

obtain the following results (in red):

Many thanks in advance for your help

Thanks

Kind regards

[MayilVahanan]

Hi

Try like below

Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL)))

[Gabbar · Accepted solution]

As you mention you wanted to do it in load Script, as per your sample data try this:-

Sales:
LOAD *,RowNo() as Sr Inline [
SalesDate,NbSales
'2023-01-10',8
'2023-02-01',4
'2023-03-15',5
'2023-04-20',9
'2023-05-02',4
'2023-06-23',2
'2023-07-12',20
];

NoConcatenate
B:
Load Sr,SalesDate,NbSales,Peek(NbSales)
, If(Sr=1,NbSales,NbSales+peek(Cumulative_Sales)) as Cumulative_Sales Resident Sales
order by Sr;

Drop Table Sales;


But i must Suggest that doing the cumulative Sum in load script might lead to multiple errors in UI because it now becomes a fixed value rather that a Calculative measure. So it might show wrong result based on Dimension Selected.

If you decide to do it in load Script above method is your way,
If you choose to do it in UI/Sheets then use :- RangeSum(Above(TOTAL Sum(NbSales), 0, RowNo(TOTAL)))

[annick]

Thanks a lot! it works well

[jmmayoral3]

HI.

The trick is to use the function 'peek' to  use a data read in a previous record. So, yo canadd a new column with the cummulative sales:

sales:
LOAD
* Inline [
SalesDate, NbSales
'2023-01-10', 8
'2023-02-01', 4
'2023-03-15', 5
'2023-04-20', 9
'2023-05-02', 4
'2023-06-23', 2
'2023-07-12', 20
];

LEFT JOIN(sales)
LOAD SalesDate,
rangesum(NbSales,peek('AccumSales')) AS AccumSales
RESIDENT sales
ORDER BY SalesDate asc;