4.25 Aggregate files - cumulate and aggregate

 

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(#),~))