MOVGCn returns a geometric mean for a specified number of days of a conditional variable; converting zeros (0) to one (1).  If daily detail variables are used returns the n "slot" moving geometric mean.

SYNTAX:

MOVGCn(Cx)
where :
Cx is a conditional variable x
n specifies the number of days to calculate the average. For daily detail variables, this specifies the number of slots (i.e., hours for hourly variables).

NOTES:

n MUST be between 1 and 100.  Spans larger than 100 days will not calculate.

Blank values and negative values are ignored.  To ignore zero (0) values, use MOVGn().

A conditional variable should be used (use Cx), meaning BLANK values are ignored.

The result will include the current date in the calculation.

This function requires the target and source variable frequencies to match. E.g. Hourly target variable = Hourly source variable, 5-Minute Target variable = 5-Minute Source Variable. If the frequencies do not match, the function will return incorrect values.

EXAMPLE 1: Return the Moving Geometric Mean for 14 days from a daily variable.

Source variable (71) is set to Daily and Target variable (116) is also set to Daily. Because both the Source and Target variables are the same (Daily), the time increment BYDAY will be used.

V116 = MOVGC14(C71)

Variable 115 will contain a moving daily geometric mean of all values in the last 14 days of variable 71:

 

Example 2: Return the seven hour Moving Geometric Mean from an hourly variable.

Source variable (4714) is set to Hourly and Target variable (4715) is set to Hourly.

V4714 - Is a hourly parameter
V4715 - Is a hourly calculation

V4715 = MOVGC7(V4714)

Our results will be as follows:

EXAMPLE #3: Calculate the 7 day moving Geometric Mean from an hourly variable into a daily calculation.

Since we are mixing variable frequencies the MOVGCn function will not work. We need to use the advanced math toolbox function GETGEOMEANC.

V2 is an hourly parameter
V3 is a daily calculation

V3 = GETGEOMEANC(C2,#MM/-6 TO EOD, BYHOUR)

When calculating Jan 7th, 2015 this calc would include data from Jan 1st 00:00 thru the last hourly slot of Jan 7th, 2015 (i.e. data for Jan 7th, 11PM slot will be included).

See: Working with Daily Detail Variables in Calculations.