Date: Sat, 4 Feb 2023 17:58:55 +0000 (UTC) Message-ID: <1088817002.37735.1675533535126@9be56cc0d034> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_37734_1363400069.1675533535126" ------=_Part_37734_1363400069.1675533535126 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - NEXT Function

# EXAMPLE - NEXT Function

The following dataset contains order information for the precedi= ng 12 months. You want to compare the current month's average against the p= receding quarter.

Source:

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
------=_Part_37734_1363400069.1675533535126--