Skip to main content

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:

Item

Description

NEXT Function

Extracts the value from a column that is a specified 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 the 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 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:

Transformation Name

Window

Parameter: Formulas

ROLLINGAVERAGE(Amount, 3, 0)

Parameter: Order by

-Date

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:

Transformation Name

Window

Parameter: Formulas

NEXT(window, 1)

Parameter: Order by

-Date

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

Transformation Name

Rename columns

Parameter: Option

Manual rename

Parameter: Column

window1

Parameter: New column name

'Amount_PriorQtr'

You can reformat this numeric value:

Transformation Name

Edit column with formula

Parameter: Columns

Amount_PriorQtr

Parameter: Formula

NUMFORMAT(Amount_PriorQtr, '###.00')

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.

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

NUMFORMAT(((Amount - Amount_PriorQtr) / Amount_PriorQtr) * 100, '##.##')

Parameter: New column name

'NetChangePct_PriorQtr'

Results:

Anmerkung

You might notice that there are computed values for 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