SI.TURNOVER

Overview

The SI.TURNOVER function returns the balance turnover of a GL account (between two dates).

Syntax

CODE

=SI.TURNOVER(ConnectionName, AccountGroup, Account, FromDate, ToDate, LocationId, Books, Dimensions, UserDefinedDimensions, IncludeChildDimensionBalances)

Arguments

The SI. TURNOVER function uses the following arguments:

Argument	Required/ Optional	Description
`ConnectionName`	Required	The name of the connection as configured in the Connection Manager
`AccountGroup`	Optional if `Account` is specified	The name of the account group for which to return the aggregated turnover. Computational Account Groups are not supported.
`Account`	Optional if `AccountGroup` is specified	The code of the general ledger account, or multiple account codes defined by a Velixo range expression
`FromDate`	Required	The inclusive start date in YYYY-MM-DD format or a cell reference to a date
`ToDate`	Required	The inclusive end date in YYYY-MM-DD format or a cell reference to a date
`LocationId`	Optional unless the company has multiple base currencies	The ID of the entity or location
`Books`	Optional	The reporting book or global consolidation book to be used for retrieving the balance. One or more user-defined adjustment book IDs may be included `Books` argument: If you leave the argument empty value, the default reporting book will be used (`ACCRUAL` or `CASH` depending on your company settings). If you specify just one reporting or global consolidation (`"GC"`) book, that's what will be used to retrieve balances. If you specify more than one reporting / GC books (e.g. `"ACCRUAL;GCBOOK"`) an error will be thrown - it is not allowed in Sage. If you specify a reporting / GC book and one or more predefined/user-defined adjustment books (e.g. `"ACCRUAL;TAX;GAAP"`) then you will retrieve balances from `ACCRUAL` plus any adjustments defined by each of the adjustment books.
`Dimensions`	Optional	Either: A two-column Excel range, where the first column contains the name of a predefined analytical dimension (e.g. department, customer, project etc.), and the second column contains the semicolon-separated list of values for that dimension. OR A multi-column array where the first row (or column) of the array contains the name of a predefined analytical dimension, and the remaining rows (or columns) contain the individual values for that dimension. (note: this feature is available in version 2023.8 and higher) The SI.DIMENSIONSfunction can make referencing dimensions much easier. For more in-depth information about filtering by Dimensions: see Using Dimensions For user-defined dimensions, use the `UserDefinedDimensions` argument.
`UserDefinedDimensions`	Optional	Either: A two-column Excel range, where the first column contains the name of a user-defined analytical dimension, and the second column contains the respective value (or a semicolon-separated list of values) for that dimension. Or A multi-column array where the first row (or column) of the array contains the name of a user-defined analytical dimension, and the remaining rows (or columns) contain the individual values for that dimension. (note: this feature is available in version 2023.8 and higher) You can specify dimension values using ranges, wildcards, and other techniques. The SI.DIMENSIONSfunction can make referencing dimensions much easier. For predefined analytical dimensions like customer, project etc., use the `Dimensions` argument.
`IncludeChildDimensionBalances`	Optional	`TRUE` or `FALSE` (Default) Determines if (for dimensions which have child dimensions) the balances of child dimensions are included in the calculation of the total balance. This argument only affects balances if: The `Location` argument is defined and/or Other dimensions are specified in the `Dimensions` or `UserDefinedDimensions` arguments. The `IncludeChildDimensionBalances` argument performs similarly to the `Include subdimensions` checkbox in Sage Intacct's stock reports such as Account Balances:

Examples

Example 1

CODE

=SI.TURNOVER("Demo", , “10100", "2022-04-01", "2022-04-30", "100", "ACCRUAL", $C$6:$D$8)

Description

Returns the turnover of the account "10100 - Cash" during April 2022, for entity "100" in the Accrual book, and filtered down by dimensions specified in the C6:D8 range (for example, by Department and Employee).

Result

300.00

(This example uses cell references for some of the arguments)

Example 2 - Dimension Array

For examples of the use of a dimension array (available in version 2023.8 and higher), see: Example 2 - Dimension Array in the SI.OPENINGBALANCE function.

Example 3 - Empty Account Group

Assuming this configuration within Sage Intacct:

No accounts are assigned to the Accumulated Depreciation account group

CODE

=SI.TURNOVER("Sage", "Accumulated Depreciation",,"2019-01-01","2019-12-31","100", "ACCRUAL")

Description

The various Velixo balance functions (SI.OPENINGBALANCE, SI.TURNOVER, SI.CLOSINGBALANCE, and SI.BUDGETTURNOVER) will return zero when there are no accounts assigned to the referenced account group.

Results

Example 4 - using Include Child Dimension Balances with Location

Below are balances where the Dimensions and UserDefinedDimensions are not used, but where a Location is specified with the IncludeChildDimensionBalances Argument:

Example 5 - Include Child Dimension Balances or not

Below are balances for a specific Location, where Dimensions and UserDefinedDimensions are not used.

Customer 10007 is defined as a child account of customer 10001.

In row 6, when excluding the balance for customer 10001, you can choose whether or not to also exclude the balance for child account 10007.

Rows 3 and 7 use two techniques (blank and "*") to achieve the same result.