RaqForum 22 No.
1 Reply  •  420 View  • 
     ...

Calculate Cumulative Value in Categories

 

Example

In the Excel file sales.xlsx, the sales amount of salespeople is shown in the following figure:

..

The task is to calculate the date when the sales amount of each salespeople is over 50,000.

Write SPL script:

A

1

=T("e:/work/sales.xlsx")

2

=A1.group(Name)

3

=A2.new(Name,~.select@1(Sales[:0].sum()>50000).Date:Date)

A1 Read the data in the file sales.xlsx as a table sequence

A2 Group the data by Name

A3 Create a new record with each group in A2, select the Name field, and loop through the records of the group. If the sum of Sales from the first row to the current row is greater than 50,000, select the Date value of this row and name it as the new Date field. Sales[:0] represents the sequence composed of the Sales fields from the first row to the current row in the group, and the @1 option means to select the first record that meets the condition

The final result in A3 is as follows:

..


 
 •  420 View  •