Average

Calculates the average value for an account across a number of periods.

Return Value

Returns a string of characters representing the correct expression to be used as part of the HS.EXP function.

Syntax

Average (PointOfView, Periods)

Table 12-3 Syntax of Average Function

Parameter Valid Values

Parameter	Valid Values
PointOfView	Valid combination of Account, Custom1….4, ICP members, for example, "A#CASH.C1#[None].I#[ICP Top]" For flow type accounts, the function averages only the periodic value.
Periods	Must be one of these values: YTD - Specify the year-to-date option to average the cumulative data from period one in the current year. Periodic - Specify the periodic option to average the current and immediately prior period in the current year only. For the first period, this value will be the same as the source. [Any whole positive number] - Specify a number of periods over which the average is to be calculated. For a rolling year average in a monthly category, specify `12`.

PointOfView

Valid combination of Account, Custom1….4, ICP members, for example, "A#CASH.C1#[None].I#[ICP Top]"

For flow type accounts, the function averages only the periodic value.

Periods

Must be one of these values:

YTD - Specify the year-to-date option to average the cumulative data from period one in the current year.

Periodic - Specify the periodic option to average the current and immediately prior period in the current year only. For the first period, this value will be the same as the source.

[Any whole positive number] - Specify a number of periods over which the average is to be calculated. For a rolling year average in a monthly category, specify 12.

Detailed Description

This function calculates the average value of an account over a specified number of prior periods. If the source is a balance type account, the average is based on the entered data. If the source is a flow type account, the average is based on the periodic data only.

The Average value is derived differently based on the Periods parameter.

If the Periods parameter is YTD, the average value is the sum of all periods in the current year up to the current divided by the current period number.
If the Periods parameter is Periodic, the average value is the sum of the current and immediately prior periods divided by two. If the current period is the first period of the year, the average value is the same value as the source.
If the Periods parameter is a number, the average value is the sum of the current and each preceding period for the specified number of periods, divided by the specified number.

Example

The SALES account will return these values for January, February, and March 2014 depending on the Periods parameter used in the Average custom function. The default view set for the scenario being processed is YTD.

Table 12-4 Example of Average Function

Account	Oct2013	Nov2013	Dec2013	Jan2014	Feb2014	Mar2014
A#Sales	9,000	10,500	11,700	800	1,900	3,200
Average ("A#Sales", "YTD)"	N/A	N/A	N/A	800	950	1,067
Average ("A#Sales", "Periodic)"	N/A	N/A	N/A	800	950	1,200
Average ("A#Sales", "3")	N/A	N/A	N/A	1,167	1,033	1,067

Sample Script

' sample statement written in the calling routine

Sub Calculate()

Hs.Exp "A#AVG_SALES = "& Average("A#Sales", "12")

End Sub

' programming of the AVERAGE function

FUNCTION Average(strPOV,strPERIOD)

DIM nPERIOD
DIM strCUM
DIM i

strPOV = UCASE(strPOV)
strPERIOD = UCASE(strPERIOD)

IF strPERIOD = "PERIODIC" THEN

IF HS.PERIOD.ISFIRST = TRUE THEN

nPERIOD = 1

ELSE

    nPERIOD = 2

END IF

ELSEIF strPERIOD = "YTD" THEN

nPERIOD = HS.PERIOD.NUMBER()

ELSEIF CINT(strPERIOD) > 0 THEN

nPERIOD = CINT(strPERIOD)

ELSE

EXIT FUNCTION

END IF

FOR i = 0 TO nPERIOD-1

IF i = 0 THEN

strCUM = strPOV &".W#PERIODIC"

ELSE

strCUM = strCUM &"+" & strPOV &".W#PERIODIC.P#CUR-" &i

END IF

NEXT

Average = "" (("" & strCUM &"")/""& nPERIOD &"")""

END FUNCTION