"There are statistical tables for the daily sales of some goods in current month, one table per d .."

mars RaqForum 25 No.
311 View • 2 Years ago

4.25 Aggregate files - cumulate and aggregate

desktop and excel data processing cases(180)

There are statistical tables for the daily sales of some goods in current month, one table per day. Now we want to add the cumulative value to the column “monthly cumulative sales” of these files.

Before merging:

20220101.xlsx
imagepng

20220102.xlsx
imagepng

20220103.xlsx
imagepng

Files of other dates are omitted.

After merging:

20220101.xlsx
imagepng

20220102.xlsx
imagepng

20220103.xlsx
imagepng

Files of other dates are omitted.

Script:

	A	B
1	2022-01-01	2022-01-31
2	=periods(A1,B1).(string(~,“yyyyMMdd”)+“.xlsx”)
3	=A2.(T(~))
4	>A3(1).run(MonthlyCumulativeSales=DailySales)
5	for A3.to(2,)	=A5.run(MonthlyCumulativeSales=DailySales+A3(#A5).select@1(Name==A5.Name). MonthlyCumulativeSales)
6	=A3.run(T(A2(#),~))