This example covers how to use the NEXT function to create windows of data from the current row and subsequent (next) rows in the dataset. You can then apply rolling computations across these windows of data. |
Functions:
Source:
The following dataset contains order information for the preceding 12 months. You want to compare the current month's average against the preceding quarter.
Date | Amount |
---|---|
12/31/15 | 118 |
11/30/15 | 6 |
10/31/15 | 443 |
9/30/15 | 785 |
8/31/15 | 77 |
7/31/15 | 606 |
6/30/15 | 421 |
5/31/15 | 763 |
4/30/15 | 305 |
3/31/15 | 824 |
2/28/15 | 135 |
1/31/15 | 523 |
Transformation:
Using the ROLLINGAVERAGE
function, you can generate a column containing the rolling average of the current month and the two previous months:
Note the sign of the second parameter and the order
parameter. The sort is in the reverse order of the Date
parameter, which preserves the current sort order. As a result, the second parameter, which identifies the number of rows to use in the calculation, must be positive to capture the previous months.
Technically, this computation does not capture the prior quarter, since it includes the current quarter as part of the computation. You can use the following column to capture the rolling average of the preceding month, which then becomes the true rolling average for the prior quarter. The window
column refers to the name of the column generated from the previous step:
Note that the order parameter must be preserved. This new column, window1
, contains your prior quarter rolling average:
You can reformat this numeric value:
You can use the following transformation to calculate the net change. This formula computes the change as a percentage of the prior quarter and then formats it as a two-digit percentage.
Results:
NOTE: You might notice that there are computed values for |
Date | Amount | Amount_PriorQtr | NetChangePct_PriorQtr |
---|---|---|---|
12/31/15 | 118 | 411.33 | -71.31 |
11/30/15 | 6 | 435.00 | -98.62 |
10/31/15 | 443 | 489.33 | -9.47 |
9/30/15 | 785 | 368.00 | 113.32 |
8/31/15 | 77 | 596.67 | -87.1 |
7/31/15 | 606 | 496.33 | 22.1 |
6/30/15 | 421 | 630.67 | -33.25 |
5/31/15 | 763 | 421.33 | 81.09 |
4/30/15 | 305 | 494.00 | -38.26 |
3/31/15 | 824 | 329.00 | 150.46 |
2/28/15 | 135 | 523.00 | -.74.19 |
1/31/15 | 523 |