- Contents
- Index
- Search
Display results with all search words
gtGLBudAvg
This function retrieves the reporting budget average balance for a GL account or GL Masterfile Tree Branch for a specified period range.
Company
The company code, or codes, that the budget averages are to be calculated from. Wildcards can be used instead to return accumulated values from all companies as long as they have the same calendars and base currencies, for example, *, or a string of company codes can be entered — for example, 01, 03.
AccOrTree
The account number or name of the tree that the period information is to be searched for. The account number may include wildcards — for example, *.*.*.1010 will accumulate everything for all accounts with a 4th segment of 1010.
TreeBranch
The tree branch name. This is left empty (null) if an account is being referenced.
Budget
The budget number that the averages are to be calculated from.
FromYear
The Year the information is to be found from.
FromPeriod
The period information is found from. If this parameter is set to 0 then the accounts financial year opening balances are included in the average.
ToYear
If not defined, then the ToYear defaults to the same as the FromYear. If defined, period information is accumulated and averaged between the FromYear and the ToYear.
ToPeriod
If not defined, the ToPeriod defaults to the same as the FromPeriod. If defined, period information is accumulated and averaged between the FromPeriod to the ToPeriod.
TransType
This determines what information is returned. Valid codes are:
- A - l financial information is totalled and returned
- Q - l unit or quantity information is totalled and returned
If this is left unspecified, the default is A.
ForeignCurrency
If True, returns the balance in the account's foreign currency. If this is left unspecified the default is false, which means the accounts local currency balance is retrieved.
CreditAccount
If True, indicates the account is normally a credit account (for example, revenue accounts) and returns credit balances as a positive number.
Search
If it contains anything then this may be a string denoting filtering options for example, = MAS.AK.SLS.* or > MAS.AK.SLS.1010,< MAS.AK.SLS.2000. The comma works as a logical and (OR's are not supported).
> or < do not work with wildcards (*).
When using the search function, you must put a space after the equal sign, regardless of whether using a wildcarded or a valid segment
YTDAvg
If True, the calculation averages the cumulative balance at each period end in the date range. If False, the calculation averages the periodic movement at each period end in the date range.
Returns
The average general ledger account balance for the specified period(s):
If a Balance Sheet account is included in the account list to average:
|
|
YTDAvg=True |
YTDAvg=False |
|
From Period=0 |
(Opening Balance + Sum of the closing balance at each period end)/ (number of periods + 1) |
(Sum of the periodic movement in each period)/(number of periods) |
|
From Period is not equal to0 |
(Sum of the closing balance at each period end)/(number of periods) |
(Sum of the periodic movement in each period)/(number of periods) |
For example, an Asset account has these balances (assuming a June Year End):
|
|
Opening Balance |
July |
August |
September |
|
YTD Balance |
10,000 |
11,000 |
12,400 |
14,000 |
|
Periodic movement |
n/a |
1,000 |
1,400 |
1,600 |
Where the user stipulates to return an average from Period 0 to September these would be the result:
|
|
YTDAvg=True. |
YTDAvg=False. |
|
From Period=0 To Period = September |
(10000 + 11000 + 12400+ 14000)/(3+1) = 11850. |
(1000 + 1400 + 1600) /3 = 1333.33. |
Where the user stipulates to return an Average from July to September these would be the result:
|
|
YTDAvg=True. |
YTDAvg=False. |
|
From Period=July To Period = September |
(11000 + 12400 + 14000) ÷ 3 = 12466.67 |
(1000 + 1400 + 1600) ÷ 3 = 1333.33. |
Where Profit and Loss accounts are only included in the account list to average:
|
|
YTDAvg=True. |
YTDAvg=False. |
|
From Period=0 |
(Sum of the closing balance at each period end)/(number of periods) |
(Sum of the periodic movement in each period)/(number of periods) |
|
From Period is not equal to 0 |
(Sum of the closing balance at each period end)/(number of periods) |
(Sum of the periodic movement in each period)/(number of periods) |
For example, an Expense account has these balances (assuming a June Year End):
|
|
Opening Balance. |
July. |
August. |
September. |
|
YTD Balance |
n/a. |
1,000. |
2,400. |
4,000. |
|
Periodic movement |
n/a. |
1,000. |
1,400. |
1,600. |
Where the user stipulates to return an average from Period 0 to September these would be the result:
|
|
YTDAvg=True. |
YTDAvg=False. |
|
From Period=0 To Period = September. |
(1000 + 2400+ 4000)/3 = 2466.67. |
(1000 + 1400 + 1600) /3 = 1333.33. |
Where the user stipulates to return an Average from July to September these would be the result:
|
|
YTDAvg=True. |
YTDAvg=False. |
|
From Period=July To Period = September. |
(1000 + 2400+ 4000)/3 = 2466.67. |
(1000 + 1400 + 1600) /3 = 1333.33. |
Examples
= gtGLActBal(01, 02,MAS.AK.SLS.1010,,2005,April)
= gtGLActBal(*,MAS.AK.SLS.5020,,2005,0,2005,August)
= gtGLActBal(01,Profit and Loss,Advertising,2005,August,2005,August,,,,*.AK.FTY.*)
Errors Returned
- Must have a Company identifier
- Invalid Company identifier
- Must have an Account or Tree identifier
- Account does not exist
- Tree does not exist
- Tree Branch does not exist
- Must have a Budget number
- Budget number does not exist
- Must have a FromYear
- FromYear does not exist
- Must have a FromPeriod
- FromPeriod does not exist
- ToYear does not exist
- ToPeriod does not exist
- ToYear is before FromYear
- ToPeriod is before FromPeriod
- Relative reference is not numeric