Contents:
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 | Window |
|---|---|
| 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 | Rename columns |
|---|---|
| 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 |
This page has no comments.