Page tree

Outdated release! Latest docs are Release 8.2: EXAMPLE - Rolling Date Functions

   

This example describes how to use the rolling computational functions:

  • ROLLINGMINDATE - Computes the rolling minimum of Date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. See ROLLINGMINDATE Function.
  • ROLLINGMAXDATE - Computes the rolling maximum of date values forward or backward of the current row within the specified column. Inputs must be of Datetime type. See ROLLINGMAXDATE Function.
  • ROLLINGMODEDATE - 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. See ROLLINGMODEDATE Function.


Source:

The following table contains an unordered list of orders:

myDateprodIdorderDollars
2020-03-13p0011445
2020-03-06p002712
2020-03-16p0031374
2020-03-23p0011675
2020-04-09p0021005
2020-08-09p003984
2020-05-02p0011395
2020-06-14p0021866
2020-07-16p003824
2020-09-02p0011785
2020-08-31p002697
2020-10-22p0031513
2020-03-17p001768
2020-03-21p0021893
2020-03-23p0031122
2020-04-06p001805
2020-05-09p0021752
2021-01-09p003616
2020-08-18p0011563
2020-09-12p002730
2020-10-04p003587
2021-02-15p0011979
2021-02-22p002134
2021-03-14p003938


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:

orderDateprodIdorderDollarsrollingMinDaterollingMaxDaterollingModeDate
3/16/20p00313743/16/203/16/203/16/20
8/9/20p0039843/16/208/9/203/16/20
7/16/20p0038243/16/208/9/203/16/20
10/22/20p00315133/16/2010/22/203/16/20
3/23/20p00311223/16/2010/22/203/16/20
1/9/21p0036163/23/201/9/213/23/20
10/4/20p0035873/23/201/9/213/23/20
3/14/21p0039383/23/203/14/213/23/20
3/13/20p00114453/13/203/13/203/13/20
3/23/20p00116753/13/203/23/203/13/20
5/2/20p00113953/13/205/2/203/13/20
9/2/20p00117853/13/209/2/203/13/20
3/17/20p0017683/13/209/2/203/13/20
4/6/20p0018053/17/209/2/203/17/20
8/18/20p00115633/17/209/2/203/17/20
2/15/21p00119793/17/202/15/213/17/20
3/6/20p0027123/6/203/6/203/6/20
4/9/20p00210053/6/204/9/203/6/20
6/14/20p00218663/6/206/14/203/6/20
8/31/20p0026973/6/208/31/203/6/20
3/21/20p00218933/6/208/31/203/6/20
5/9/20p00217523/21/208/31/203/21/20
9/12/20p0027303/21/209/12/203/21/20
2/22/21p0021343/21/202/22/213/21/20

This page has no comments.