ACCOUNTTURNOVER

Overview

The ACCOUNTTURNOVER function calculates the turnover of one or more general ledger account(s) as of a given period.

This is particularly useful for creating YTD, MTD, or QTD reports.

Syntax

=ACCOUNTTURNOVER(
    ConnectionName, 
    Ledger, 
    AccountClass, 
    Account, 
    Subaccount, 
    Branch, 
    FromPeriodOrDate, 
    ToPeriodOrDate, 
    IncludeUnposted, 
    UseMasterFinancialCalendar
)

Arguments

The ACCOUNTTURNOVER function uses the following arguments (see our articles on Filtering Velixo Functions and using arrays or cell ranges as arguments):

Argument	Required/Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`Ledger`	Required	Ledger ID. Leverage the ACU.EXPANDLEDGERRANGE function to obtain available values. Supports Velixo filtering techniques. When used with Budget ledgers, the `ACCOUNTTURNOVER` function returns only released budget data
`AccountClass`	Optional, though required if `Account` is empty	Account class ID. Leverage the EXPANDACCOUNTCLASSRANGE function to obtain available values. Supports Velixo filtering techniques.
`Account`	Optional, though required if `AccountClass` is empty	General ledger account code(s). Leverage the EXPANDACCOUNTRANGE function to obtain available values. Supports Velixo filtering techniques.
`Subaccount`	Optional	Subaccount code(s). Leverage the EXPANDSUBACCOUNTRANGE function to obtain available values. Supports Velixo filtering techniques.
`Branch`	Optional	Branch ID(s). Leverage the EXPANDBRANCHRANGE function to obtain available values. Supports Velixo filtering techniques.
`FromPeriodOrDate`	Required	The beginning financial period, in MM-YYYY format or a reference to a cell containing the beginning date in a valid Excel date format
`ToPeriodOrDate`	Required	The ending financial period, in MM-YYYY format. or a reference to a cell containing the ending date in a valid Excel date format
`IncludeUnposted`	Optional	1 - Include posted transactions only (default) 2 - Include Unposted transactions only 3 - Include Posted and Unposted transactions Transactions with a status of "On Hold" are excluded. When Unposted transactions are included, the following two statuses are included: Balanced, Unposted If a document is still in the AR module (e.g., Invoice), the corresponding GL batch has not yet been created, thus the balance for that document will not be reflected). Excel for Windows allows the use of quotation marks around this argument. Excel for Mac and Excel Online require that it be unquoted.
`UseMasterFinancialCalendar`	Optional	Use Acumatica's Master Financial Calendar instead of the financial calendar defined within the specific tenant associated with the connection being accessed (this can be useful for consolidation reports). Possible values: `TRUE` `FALSE` (default)

Examples

Given this data:

Example - Posted transactions for an account

=ACCOUNTTURNOVER(
    "Demo", 
    "ACTUAL",
    , 
    "10700", 
    , 
    , 
    "01-2019", 
    "01-2019"
)

Description: Calculates the turnover of all posted transactions to General Ledger account #10700 (as noted in cell B8) during the first financial period of year 2019 (as noted in cell G1).

Result: 54,873

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/kIlJLxS04JN5T9eozlhJ481v7APq9Q8u2A.png

Example - Posted and Unposted transactions

=ACCOUNTTURNOVER(
    "Demo", 
    "ACTUAL",
    , 
    "10600", 
    , 
    , 
    $C$4, 
    $D$4, 
    3
)

Description: Calculates the turnover of all posted and unposted transactions to General Ledger account #10600 (as noted in cell B7) between January 1, 2019 (as noted in cell C4) and January 15, 2019 (as noted in cell D4).

Result: 3,325

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/xYgEViSOcJAj4JukcAbVBchi-SZwsmU4fg.png

Example - Released budget values

=ACCOUNTTURNOVER(
    "Demo", 
    "BUDGET",
    , 
    "10600", 
    , 
    "*", 
    "12-2019", 
    "12-2019"
)

Description: Retrieves the released value in the Budget ledger for General Ledger account #10600 for all branches for period 12-2019

Result: 493,710

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/vjbrAsZe9Faw4JgKUsc4gIzR6Z42PfIIFg.png

Cell references were used for arguments in this example

Any UN-released budget values (as shown on the ERP's Release Budgets screen) will *NOT* be reflected in the balance. Only released budget values can be retrieved.

Limitations to filtering by date instead of the financial period

This feature cannot be used with the Net Income account
Drilldown will show transactions for the entire financial period (you will then need to manually filter on the date column)
Balances are calculated based on the total from the nearest financial period, and then adjusted based on the filtered transaction date
Balances will not reflect transactions within the specified date range but which have been posted to a different financial period.