1. Administrator's Guide
  2. Custom Functions
  3. Management Reporting Functions
  4. Cumulative

Cumulative

Calculates the total of the preceding period’s values for a specified account.

Return Value

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

Syntax

Cumulative (PointOfView, View, NumPeriod)

Table 12-5 Syntax of Cumulative Function

Parameter Valid Values

PointOfView

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

View

Must be one of these values:

"" " " (double quote) - Based on the default view defined for the scenario being processed (either YTD or Periodic).

YTD - User specifies the Year-to-date option, which overrides the default view set for the scenario.

Periodic - Specify the periodic option, which overrides the default view set for the scenario.

NumPeriod

A whole number representing the number of periods in the current scenario to accumulate, starting with the current period.

If NumPeriod is 0 or negative, the function aggregates from the beginning of the current year.

Detailed Description

This function calculates the sum of either the periods specified or the sum year to date for the specified account. By default, the view of the accumulated data is the scenario default; however, you can override this for flow type accounts.

  • If the View parameter is YTD, the function accumulates the year-to-date values.

  • If the View parameter is Periodic, the function accumulates the periodic values.

  • If the View parameter is blank ( " " ), the function accumulates the data using the scenario default view.

Example

The CASH account will return the following values for January, February, and March 2014 depending on the Number parameter used in the Cumulative function.

The a SALES account will return the following values for January, February, and March 2014 depending on both the View and Number parameters used in the Cumulative function. The default view set for the scenario being processed is YTD.

Table 12-6 Example of Cumulative Function

Account Oct2013 Nov2013 Dec2013 Jan2014 Feb2014 Mar2014

A#Cash

1,000

1,500

1,200

800

1,100

1,300

Cumulative ("A#Cash","0")

N/A

N/A

N/A

800

1,900

3,200

Cumulative("A#Cash", "3")

N/A

N/A

N/A

3,500

3,100

3,200

A#Sales

9,000

10,500

11,700

800

1,900

3,200

Cumulative("A#Sales", "0")

N/A

N/A

N/A

800

2,700

5,900

Cumulative("A#Sales", "Periodic", "0")

N/A

N/A

N/A

800

1,900

3,200

Cumulative("A#Sales", "Periodic", "3")

N/A

N/A

N/A

3,500

3,100

3,200

Sample Script

' sample statement written in the calling routine
Sub Calculate()
HS.EXP "A#TOT_Cash =" &Cumulative("A#Cash","" ,0)
End Sub
' programming of the Cumulative function
Function Cumulative(StrPov, StrVIEW, nPERIOD)
DIM strCUM
DIM i
IF nPERIOD <= 0 THEN
nPERIOD = HS.PERIOD.NUMBER() - 1
ELSE
nPERIOD = nPERIOD - 1
END IF
IF strVIEW = "" THEN 
strVIEW = HS.SCENARIO.DEFAULTVIEW("") 
END IF
strPOV = UCASE(strPOV)
strVIEW = UCASE(strVIEW)
IF strVIEW = "PERIODIC" THEN
strVIEW = ".W#PERIODIC"
         
ELSEIF strVIEW =  "YTD" THEN
strVIEW = ".W#YTD"
         
ELSE
EXIT FUNCTION
END IF
FOR i = 0 TO nPERIOD
IF i = 0 THEN
strCUM = strPOV & strVIEW
ELSE
strCUM = strCUM &"+" & strPOV & strVIEW &".P#CUR-" &i
END IF
NEXT
  Cumulative = ""("" & strCUM &"")
END FUNCTION