How to Select Records by Specified Percentage of a Specific Field

 

Task description

Sales table sales.xlsx contains data as follows:

undefined

The task is to find product whose sales revenue occupies over 70% of the total revenue in the current month.

 

Directions:

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Write script in esProc:

undefined

List the code separately for an easy viewing:

A

1

=clipboard().import@t().derive(Percentage)

2

=A1.group(SalesName,Month)

3

=A2.run(a=~.sum(Revenue),~.run(Percentage=Revenue/a*100))

4

=A1.select(Percentage>70).new(SalesName,Product,Month,Revenue)

5

=clipboard(A4.export@t())

A1   Percentage Import data from clipboard and add a new column; @t enables reading the first row as column headers.

A2   Group rows by SalesName and Month.

A3   Loop through each group to sum Revenues and assign the result to variable a, and then loop over each record in each group to calculate the Percentage.

A4   Get records where Percentage is greater than 70, and select SalesName, Product, Month, and Revenue columns to create a new data set.

A5   Convert A4’s result set to strings and export them to clipboard. @t option enables reading the first row as column headers.

 

3. Switch back to Excel, select data area A1:D13, and press Ctrl+C to copy it to clipboard.

In esProc, press F9 to execute the program, and switch back to Excel after the execution is finished to click F1, and press Ctrl+V to paste the result in.

    undefined

 

 

Reference percentage.zip