EXAMPLE - Rolling Date Functions
This example describes how to use rolling functions for Datetime values.
Functions:
Item | Description |
|---|---|
ROLLINGMINDATE Function | Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. |
ROLLINGMAXDATE Function | Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. |
ROLLINGMODEDATE Function | Computes the rolling mode (most common value) forward or backward of the current row within the specified column. Input values must be of Datetime data type. |
Source:
The following table contains an unordered list of orders:
myDate | prodId | orderDollars |
|---|---|---|
2020-03-13 | p001 | 1445 |
2020-03-06 | p002 | 712 |
2020-03-16 | p003 | 1374 |
2020-03-23 | p001 | 1675 |
2020-04-09 | p002 | 1005 |
2020-08-09 | p003 | 984 |
2020-05-02 | p001 | 1395 |
2020-06-14 | p002 | 1866 |
2020-07-16 | p003 | 824 |
2020-09-02 | p001 | 1785 |
2020-08-31 | p002 | 697 |
2020-10-22 | p003 | 1513 |
2020-03-17 | p001 | 768 |
2020-03-21 | p002 | 1893 |
2020-03-23 | p003 | 1122 |
2020-04-06 | p001 | 805 |
2020-05-09 | p002 | 1752 |
2021-01-09 | p003 | 616 |
2020-08-18 | p001 | 1563 |
2020-09-12 | p002 | 730 |
2020-10-04 | p003 | 587 |
2021-02-15 | p001 | 1979 |
2021-02-22 | p002 | 134 |
2021-03-14 | p003 | 938 |
Transformation:
You can use the following Window transformation to calculate the rolling minimum, maximum, and mode dates for the last five orders for each product identifier:
Transformation Name |
|
|---|---|
Parameter: Formula1 | ROLLINGMINDATE(orderDate, 4, 0) |
Parameter: Formula2 | ROLLINGMAXDATE(orderDate, 4, 0) |
Parameter: Formula3 | ROLLINGMODEDATE(orderDate, 4, 0) |
Parameter: Group by | prodId |
Parameter: Order by | prodId |
You can use the following transformation to rename the generated window columns:
Transformation Name |
|
|---|---|
Parameter: Option | Manual rename |
Parameter: Column | window1 |
Parameter: New column name | rollingMinDate |
Parameter: Parameter: Column | window2 |
Parameter: New column name | rollingMaxDate |
Parameter: Parameter: Column | window3 |
Parameter: New column name | rollingModeDate |
Results:
orderDate | prodId | orderDollars | rollingMinDate | rollingMaxDate | rollingModeDate |
|---|---|---|---|---|---|
3/16/20 | p003 | 1374 | 3/16/20 | 3/16/20 | 3/16/20 |
8/9/20 | p003 | 984 | 3/16/20 | 8/9/20 | 3/16/20 |
7/16/20 | p003 | 824 | 3/16/20 | 8/9/20 | 3/16/20 |
10/22/20 | p003 | 1513 | 3/16/20 | 10/22/20 | 3/16/20 |
3/23/20 | p003 | 1122 | 3/16/20 | 10/22/20 | 3/16/20 |
1/9/21 | p003 | 616 | 3/23/20 | 1/9/21 | 3/23/20 |
10/4/20 | p003 | 587 | 3/23/20 | 1/9/21 | 3/23/20 |
3/14/21 | p003 | 938 | 3/23/20 | 3/14/21 | 3/23/20 |
3/13/20 | p001 | 1445 | 3/13/20 | 3/13/20 | 3/13/20 |
3/23/20 | p001 | 1675 | 3/13/20 | 3/23/20 | 3/13/20 |
5/2/20 | p001 | 1395 | 3/13/20 | 5/2/20 | 3/13/20 |
9/2/20 | p001 | 1785 | 3/13/20 | 9/2/20 | 3/13/20 |
3/17/20 | p001 | 768 | 3/13/20 | 9/2/20 | 3/13/20 |
4/6/20 | p001 | 805 | 3/17/20 | 9/2/20 | 3/17/20 |
8/18/20 | p001 | 1563 | 3/17/20 | 9/2/20 | 3/17/20 |
2/15/21 | p001 | 1979 | 3/17/20 | 2/15/21 | 3/17/20 |
3/6/20 | p002 | 712 | 3/6/20 | 3/6/20 | 3/6/20 |
4/9/20 | p002 | 1005 | 3/6/20 | 4/9/20 | 3/6/20 |
6/14/20 | p002 | 1866 | 3/6/20 | 6/14/20 | 3/6/20 |
8/31/20 | p002 | 697 | 3/6/20 | 8/31/20 | 3/6/20 |
3/21/20 | p002 | 1893 | 3/6/20 | 8/31/20 | 3/6/20 |
5/9/20 | p002 | 1752 | 3/21/20 | 8/31/20 | 3/21/20 |
9/12/20 | p002 | 730 | 3/21/20 | 9/12/20 | 3/21/20 |
2/22/21 | p002 | 134 | 3/21/20 | 2/22/21 | 3/21/20 |