Date: Sun, 16 Jan 2022 10:13:05 +0000 (GMT) Message-ID: <1933259190.104160.1642327985886@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_104159_1432276076.1642327985886" ------=_Part_104159_1432276076.1642327985886 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - NEXT Function

EXAMPLE - NEXT Function

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:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Item Description
NEXT Function Extracts the value from a column that is a spec= ified number of rows after the current value.
ROLLINGAVERAGE Function Computes the rolling average of values forward = or backward of the current row within the specified column.
NUMFORMAT Function Formats a numeric set of values according to th= e specified number formatting. Source values can be a literal numeric value= , a function returning a numeric value, or reference to a column containing= an Integer or Decimal values.

Source:

The following dataset contains order information for the preceding= 12 months. You want to compare the current month's average against the pre= ceding 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 genera= te a column containing the rolling average of the current month and the two= previous months:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 =20 =20 =20 =20
Transformation Name = Window ROLLINGAVERAGE(Amount, 3, 0) -Date
=20

Note the sign of the second parameter and the order&nb= sp;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 calcula= tion, 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, wh= ich then becomes the true rolling average for the prior quarter. The <= code>window column refers to the name of the column generated f= rom the previous step:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name = Window NEXT(window, 1) -Date
=20

Note that the order parameter must be preserved. This new column, <= code>window1, contains your prior quarter rolling average:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Parameter: Column Parameter: Option=20 Manual rename window1 'Amount_PriorQtr'
=20

You can reformat this numeric value:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name Edit column with formula Amount_PriorQtr NUMFORMAT(Amount_PriorQtr, '###.00')
=20

You can use the following transformation to calculate the net change. Th= is formula computes the change as a percentage of the prior quarter and the= n formats it as a two-digit percentage.

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula Single row formula NUMFORMAT(((Amount - Amount_PriorQtr) / Am= ount_PriorQtr) * 100, '##.##') 'NetChangePct_PriorQtr'
=20

Results:

NOTE: You might notice that there are computed values f= or Amount_PriorQtr for February and March. These values do not= factor in a full three months because the data is not present. The January= value does not exist since there is no data preceding it.

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

=20
=20

=20

=20
• =20
=20 Page:= =20
=20
=20 NEXT Function= =20
• =20
• =20
=20 Page:= =20
=20
• =20
=20

=20
=20
=20

------=_Part_104159_1432276076.1642327985886--