Date: Fri, 19 Aug 2022 16:15:14 +0000 (UTC) Message-ID: <368586219.45589.1660925714042@93e1396c9615> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_45588_1342672365.1660925714041" ------=_Part_45588_1342672365.1660925714041 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html ROLLINGSUM Function

# ROLLINGSUM Function

=20

Contents:

=20

=20
=20
=20

=20
=20
Computes the rolling sum of values forward or backward of the current= row within the specified column.

• If an input value is missing or null, it is not factored in the computa= tion. For example, for the first row in the dataset, the rolling sum of pre= vious values is the value in the first row.
• The row from which to extract a value is determined by the order in = which the rows are organized based on the `order` parameter.&nbs= p;If you are working on a randomly generated sample of your dataset, t= he values that you see for this function might not correspond to the values= that are generated on the full dataset during job execution.

• The function takes a column name and two optional integer paramete= rs that determine the window backward and forward of the current row.
• The default integer parameter values are `-1` and&= nbsp;`0`, which computes the rolling average from the current ro= w back to the first row of the dataset.
• This function works with the following transforms:

## Basic Usa= ge

=20

Column example:

derive type:single value:ROLLINGSUM(myCol)

Output: Generates a new column containing the r= olling sum of all values in the `myCol` column from th= e first row of the dataset to the current one.

Rows before example:

=20

window value:ROLLINGSUM(myNumber, 3)

Output: Generates the new column, which contains the ro= lling sum of the current row and the two previous row values in the ```m= yNumber``` column.

Rows before and after example:

=20

window value:ROLLINGSUM(myNumber, 3, 2) <= /p>

Output: Generates the new column , which contains the rolling sum of the two previous row values, the cur= rent row value, and the two rows after the current one in the ```my= Number``` column.     = ;

## = Syntax and Arguments

=20

=20

window value:ROLLINGSUM(col_ref, ro= wsBefore_integer, rowsAfter_integer) order: order_col [group: group_c= ol]

=20 =20 =20 =20 =20
Argument Required? Data Type Description
col_ref Y string Name of column whose values are applied to the f= unction
rowsBefore_integer N integer Number of rows before the current one to include= in the computation
rowsAfter_integer N integer Number of rows after the current one to include = in the computation

For more information on the `order` and ```= group``` parameters, see Window Transform.

For more information on syntax standards, see Language Documentation Syntax Note= s.

### col_ref

Name of the column whose values are used to compute the rolling sum.&nbs= p;

• Multiple columns and wildcards are not supported.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (column reference to Integer or Decimal v= alues) `myColumn`

### ro= wsBefore_integer, rowsAfter_integer

Integers representing the number of rows before or after the curre= nt one from which to compute the rolling sum, including the current row. Fo= r example, if the first value is `5`, the current row and t= he four rows after it are used in the computation. Negative val= ues for `k` compute the rolling average from rows prec= eding the current one.

• `rowBefore=3D1` generates the current row value only.
• `rowBefore=3D-1` uses all rows preceding the current on= e.
• If `rowsAfter` is not specified, then the value 0 is applied.
• If a `group` parameter is applied, then these parameter valu= es should be no more than the maximum number of rows in the groups.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
No Integer `4`

## Examples=

=20
=20
=20

=20

### Example - Roll= ing window functions

This example describes how to use the rolling computational functions:

• `ROLLINGSUM` - computes a rolling sum from a window of rows = before and after the current row. See ROLLINGSUM Function.
• `ROLLINGAVERAGE` - computes a rolling average from a window = of rows before and after the current row. See ROLLINGAVERAGE Function.
• `ROWNUMBER` - computes the row number for each row, as deter= mined by the ordering column. See ROWNUMBER Function.

The following dataset contains sales data over the final quarter of the = year.

Source:

=20 =20 =20
Date Sales
10/2/16 200
10/9/16 500
10/16/16 350
10/23/16 400
10/30/16 190
11/6/16 550
11/13/16 610
11/20/16 480
11/27/16 660
12/4/16 690
12/11/16 810
12/18/16 950
12/25/16 1020
1/1/17 680

Transform:

First, you want to maintain the row information as a separate column. Si= nce data is ordered already by the `Date` column, you can use th= e following:

window value:ROWNUMBER() order:Date

R= ename this column to `rowId` for week of quarter.

Now, you want to extract month and week information from the ```= Date``` values. Deriving the month value:

derive type:single value:MONTH(Date= ) as:'Month'

Deriving the quarter value:=20

derive type:single value:(1 + FLOOR= (((month-1)/3))) as:'QTR'

Deriving the week-of-quarter value:=20

window value:ROWNUMBER() order:Date group:QTR

Rename this column `WOQ` (week of quarter).

Deriving the week-of-month value:

window value:ROWNUMBER() group:Month order:Date =

Rename this column `WOM` (week of month).

Now, you perform your rolling computations. Compute the running total of= sales using the following:

window value: ROLLINGSUM(Sales, -1, 0) order: Da= te group:QTR

The `-1` parameter is used in the a= bove computation to gather the rolling sum of all rows of data from the cur= rent one to the first one. Note that the use of the `QTR`&n= bsp;column for grouping, which moves the value for the ```01/01/201= 7``` into its own computational bucket. This may or may not be preferre= d.

Rename this column `QTD` (quarter to-date). Now, generat= e a similar column to compute the rolling average of weekly sales for the q= uarter:

window value: ROUND(ROLLINGAVERAGE(Sales, -1, 0)= ) order: Date group:QTR

Since the `ROLLINGAVERAGE` fu= nction can compute fractional values, it is wrapped in the ```ROUND function for neatness. Rename this column avgWeekByQuart= er. Results: When the unnecessary columns are dropped and some reordering is applied,= your dataset should look like the following: Date WOQ Sales QTD avgWeekByQuarter 10/2/16 1 200 200 200 10/9/16 2 500 700 350 10/16/16 3 350 1050 350 10/23/16 4 400 1450 363 10/30/16 5 190 1640 328 11/6/16 6 550 2190 365 11/13/16 7 610 2800 400 11/20/16 8 480 3280 410 11/27/16 9 660 3940 438 12/4/16 10 690 4630 463 12/11/16 11 810 5440 495 12/18/16 12 950 6390 533 12/25/16 13 1020 7410 570 1/1/17 1 680 680 680 =20 =20 See Also for ROLLINGSUM Function:=20 =20 =20 Page:<= /span>=20 =20 NEXT Function=20 =20 =20 Page:<= /span>=20 =20 PREV Function=20 =20 =20 Page:<= /span>=20 =20 ROWNUMBER Function=20 =20 =20 Page:<= /span>=20 =20 COALESCE Function=20 =20 =20 Page:<= /span>=20 =20 ROLLINGKTHLARGEST Function=20 =20 =20 Page:<= /span>=20 =20 ROLLINGKTHLARGESTUNIQUE Function=20 =20 =20 Page:<= /span>=20 =20 ROLLINGMIN Function=20 =20 =20 Page:<= /span>=20 =20 ROLLINGSTDEV Function=20 =20 =20 Page:<= /span>=20 =20 ROLLINGVAR Function=20 =20 =20 Page:<= /span>=20 =20 Window Functions=20 =20 =20 =20 =20 =20   ```
``` ------=_Part_45588_1342672365.1660925714041-- ```