Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)))
Hi
Try like below
Rangesum(Above(Sum(NbSales),0, RowNo(TOTAL)))
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)))
Thanks a lot! it works well
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;