2.17 Iterative aggregation
Calculate the cumulative transaction amount on each transaction date in the year 2019 based on the following SSE Composite Index table.
| Date | Open | Close | Amount |
|---|---|---|---|
| 2019/12/31 | 3036.3858 | 3050.124 | 2.27E11 |
| 2019/12/30 | 2998.1689 | 3040.0239 | 2.67E11 |
| 2019/12/27 | 3006.8517 | 3005.0355 | 2.58E11 |
| 2019/12/26 | 2981.2485 | 3007.3546 | 1.96E11 |
| 2019/12/25 | 2980.4276 | 2981.8805 | 1.9E11 |
| … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =T(“000001.xlsx”) |
| 2 | =A1.select(year(Date)==2019).sort(Date) |
| 3 | =A2.derive(cum(Amount):CUM) |
A1 Load data table.
A2 Get records of 2019 and sort them by Date.
A3 Use cum() function to calculate the cumulative transaction amount.
Execution result:
| Date | Open | Close | Amount | CUM |
|---|---|---|---|---|
| 2019/01/02 | 2497.8805 | 2465.291 | 9.759E10 | 9.759E10 |
| 2019/01/03 | 2461.7829 | 2464.3628 | 1.07E11 | 2.046E11 |
| 2019/01/04 | 2446.0193 | 2514.8682 | 1.39E11 | 3.436E11 |
| 2019/01/07 | 2528.6987 | 2533.0887 | 1.46E11 | 4.896E11 |
| 2019/01/08 | 2530.3001 | 2526.4622 | 1.23E11 | 6.126E11 |
| … | … | … | … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
