...

Summarize date by year and month

 

Example

There is an Excel file Book1.xlsx, the data is as follows:

date

val

2018/1/1

1

2019/1/1

2

2019/2/1

3

2019/2/5

4

2019/3/5

5

2019/3/10

6

2019/3/19

7

2019/3/19

8

2019/4/15

9

2019/4/29

10

 

Summarize the sum of val according to the year and month of date, and the result is as follows:

YM

total

201801

1

201901

2

201902

7

201903

26

201904

19

 

Write SPL script:

A

1

=file("Book1.xlsx").xlsimport@t()

2

=A1.groups(month@y(date):YM;sum(val):total)

 

A1 Read excel file

 

A2 date group by year and month, calculate the sum of val, month's @y represents the part with year in the parameter, and returns 6 digits


 
 •  197 View  •