Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0810

The following dataset contains order information for the preceding 12 months. You want to compare the current month's average against the preceding quarter. 

Source:

DateAmount
12/31/15118
11/30/156
10/31/15443
9/30/15785
8/31/1577
7/31/15606
6/30/15421
5/31/15763
4/30/15305
3/31/15824
2/28/15135
1/31/15523

Transformation:

Using the ROLLINGAVERAGE function, you can generate a column containing the rolling average of the current month and the two previous months:

D trans
RawWrangletrue
Typestep
WrangleTextwindow value: ROLLINGAVERAGE(Amount, 3, 0) order: -Date
p01NameFormulas
p01ValueROLLINGAVERAGE(Amount, 3, 0)
p02NameOrder by
p02Value-Date
SearchTermWindow

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:

D trans
RawWrangletrue
Typestep
WrangleTextwindow value: NEXT(window, 1) order: -Date
p01NameFormulas
p01ValueNEXT(window, 1)
p02NameOrder by
p02Value-Date
SearchTermWindow

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

D trans
RawWrangletrue
p03Value'Amount_PriorQtr'
Typestep
WrangleTextrename col:window1 to:'Amount_PriorQtr'
p01NameOption
p01ValueManual rename
p02NameColumn
p02Valuewindow1
p03NameNew column name
SearchTermRename columns

You can reformat this numeric value:

D trans
RawWrangletrue
Typestep
WrangleTextset col:Amount_PriorQtr value:NUMFORMAT(Amount_PriorQtr, '###.00')
p01NameColumns
p01ValueAmount_PriorQtr
p02NameFormula
p02ValueNUMFORMAT(Amount_PriorQtr, '###.00')
SearchTermEdit column with formula

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.

D trans
RawWrangletrue
p03Value'NetChangePct_PriorQtr'
Typestep
WrangleTextderive type:single value:NUMFORMAT(((Amount - Amount_PriorQtr) / Amount_PriorQtr) * 100, '##.##') as:'NetChangePct_PriorQtr'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueNUMFORMAT(((Amount - Amount_PriorQtr) / Amount_PriorQtr) * 100, '##.##')
p03NameNew column name
SearchTermNew formula

Results:

Info

NOTE: 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.

DateAmountAmount_PriorQtrNetChangePct_PriorQtr
12/31/15118411.33-71.31
11/30/156435.00-98.62
10/31/15443489.33-9.47
9/30/15785368.00113.32
8/31/1577596.67-87.1
7/31/15606496.3322.1
6/30/15421630.67-33.25
5/31/15763421.3381.09
4/30/15305494.00-38.26
3/31/15824329.00150.46
2/28/15135523.00-.74.19
1/31/15523