=20

=20

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

=20

=20
=20
- =20
- Basic Usage =20
- Syntax and Arguments= =20 =20
- Examples=20 =20

=20

- 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.

- The default integer parameter values are
- This function works with the following transforms:

**Column example:**

derive type:single value:ROLLINGSUM(myCol)

`myCol`

column from th=
e first row of the dataset to the current one.
**Rows before example:**

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:**

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

**Output:** Generates the new column ```
my=
Number
```

column. =
;

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.

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

Required? | Data Type | Example Value |
---|---|---|

Yes | String (column reference to Integer or Decimal v= alues) | `myColumn` |

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 value0 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

Required? | Data Type | Example Value |
---|---|---|

No | Integer | `4` |

=20

=20

**Tip:** For additional examples, see Common Tasks.

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:=20derive type:single value:(1 + FLOOR= (((month-1)/3))) as:'QTR'

Deriving the week-of-quarter value:=20window 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

```
------=_Part_45588_1342672365.1660925714041--
```