Rolling in Ruby

The traditional approach to rolling or moving averages in a cross tabulation is to construct a moving average variable which multiply counts the periods. Such a construction for MA4 on a Quarter variable would be like

1=Qrtr(1)
2=Qrtr(1/2)
3=Qrtr(1/3)
4=Qrtr(1/4)
5=Qrtr(2/5)
6=Qrtr(3/6)
…
n=Qrtr(n-3/n)

You can easily implement this using the Line Generator from the Construct Variables form:

This delivers the filters as

Quarter(-2/1) evaluates the same as Quarter(1/1) because there are no codes -2, -1 or 0. The above is constructed under any name you want – I have used QrtrMA4.

The case data at the transition to Quarter=2 is

To see how QrtrMA4 aggregates, here is QrtrMA4 by Gender filtered to Top of Mind Ad Awareness Brand 1. (The filter is to get some lumpiness in the base and frequency values.)

Comparing to the unrolled version using just the Quarter variable:

Looking at the columns for Apr2001, for the base (Cases WF) row, 201=105+96, and for Males, 106=55+51, and for females 95=50+45. Similarly, Jul2001 is the sum of itself and the preceding two quarters, Oct2001 is the sum of itself and the preceding three quarters, and thereafter always itself and the preceding three quarters.

If I use the Ruby in-built rolling on the second table above, I get

The base and frequencies are quite different to the first table above (using QrtrMA4), but THE PERCENTAGES ARE THE SAME.

The difference is in fact quite superficial. Whereas using QrtrMA shows 201=105+96 at Apr2001, the Ruby roll shows the next step, which is 201/2=100.5. Similarly, for Males, 106/2=53. And 53 as a percentage of 100.5 = 5300/100.5 = 52.73 = 52.7.

Using QrtrMA4, the calculation for 52.7 is 100*(55+51)/(105+96).

Using Ruby roll, the calculation for 52.7 is 100*((55+51)/2)/((105+96)/2).

In short, Ruby rolling shows the actual moving averages for base and frequency, whereas a constructed roll shows the intermediate sum.

Since both methods arrive at the same percentages, what are the pros and cons?

The advantages of a constructed roll are

1. More familiar – users are sometimes confused by the Ruby rolled bases not stepping up during the roll-in (even though the percentage output is identical)
2. Won’t get caught by having a base column
3. Can adjust the periods explicitly – eg you might call it a 3 month roll, but actually have one month unrolled because it was a big booster month
4. Can decide how to handle out-of-field periods – carry over if the gap is short, or start again if it is large

The disadvantages are you need a new construction, and if you want rolled and unrolled versions of the same report, you will need two discrete reports under different names, because the top axis is a different variable. This could double processing time for report generation.

The advantages of using a Ruby roll are

1. It’s automatic, and be used interactively (especially useful on charts)
2. Does not cost another variable
3. Can be applied differently per row (or column)
4. Can switch between handling out-of-field periods by carrying over or by restarting

The disadvantages are no way to adjust for booster weeks or custom control over out-of-field, and all columns (or rows) are used as inputs, so you cannot prevent extra vectors such as a base column or totals from being included in the roll.