Accumulative sum and group

UlkarA — Wed, 30 Mar 2022 12:26:29 GMT

Hi everyone , I need to Accumulative sum , but this accumulation should be grouped by date (in my situation by month). I have script as below:

result:NoConcatenate Load * InLine [SumVal];

BOX:
NoConcatenate
load distinct Date(Floor(MonthStart(TRN_DT)) ,'YYYY MMM') as DATE_REPORT
resident TRN_last
where DRCR_IND='D'
;
LET vNum=0;
LET NumRows=NoOfRows('BOX');

FOR i=0 to $(NumRows)

LET vBox=Peek('DATE_REPORT',$(i));
LET vNum=vNum+1;
trace '$(vBox)';
trace '$(vBox)';
tmp1:
NoConcatenate
load Date(Floor(MonthStart(TRN_DT)) ,'YYYY MMM') as DATE_REPORT, COMP_CUST_NO as comp_no,COMP_CUST_NAME as comp_name
, sum(LCY_AMOUNT) as SumVal
resident TRN_last
where DRCR_IND='D' and Date(Floor(MonthStart(TRN_DT)) ,'YYYY MMM')='$(vBox)'
group by Date(Floor(MonthStart(TRN_DT)) ,'YYYY MMM'), COMP_CUST_NO,COMP_CUST_NAME ;

Concatenate(result)
load DATE_REPORT,comp_no,comp_name,SumVal,
rangesum(SumVal,peek('AccSumVal')) as AccSumVal, //result field
RowNo() as row_no,
AutoNumber(RowNo(),DATE_REPORT) as auto_num
resident tmp1
order by SumVal desc
;
drop table tmp1;

NEXT;

For example in second line result of AccSumVal should be (21626178,83 + 14 664 838,04)=36 291 016,87 but i dont understand why, result same as SumVal field

Re: Accumulative sum and group

vinieme12 — Mon, 18 Apr 2022 08:34:47 GMT

Concatenate(result)
load DATE_REPORT,comp_no,comp_name,SumVal,

Remove the single quote

RangeSum(SumVal,Peek(AccSumVal)) as AccSumVal //result field

RowNo() as row_no,
AutoNumber(RowNo(),DATE_REPORT) as auto_num
resident tmp1
order by SumVal desc
;
drop table tmp1;

Re: Accumulative sum and group

undertess146 — Mon, 18 Apr 2022 08:43:18 GMT

https://tacobellbreakfastmenu.info/

topic Accumulative sum and group in App Development

Accumulative sum and group

Re: Accumulative sum and group

Re: Accumulative sum and group