Calculating totals by balance fields

BalanceAndTurnovers virtual tables of accumulation registers and accounting registers allow you to get balances for the desired period. 1C:Enterprise query language provides automatic calculation of totals for groupings by balance fields. This article covers how totals by balance fields are calculated and demonstrates some of the specifics of such calculations.

Totals by balance fields

To calculate totals by a balance field, it is sufficient to get the field in a query selection list and specify that the totals should be calculated by this field.

Example:

SELECT
    Warehouse AS Warehouse,
    Items AS Items,
    Period AS Period,
    QuantityOpeningBalance AS QuantityOpeningBalance,
    QuantityTurnover AS QuantityTurnover,
    QuantityClosingBalance AS QuantityClosingBalance
FROM
    AccumulationRegister.ItemsAccounting.BalanceAndTurnovers(, , Week, , ) AS ItemsAccountingBalanceAndTurnovers
TOTALS
    SUM(QuantityOpeningBalance), SUM(QuantityTurnover), SUM(QuantityClosingBalance)
BY
    Warehouse,
    Period,
    Items

During the query execution, the platform automatically identifies the QuantityOpeningBalance field as the one that contains the opening balance and the QuantityClosingBalance field as the one that contains the closing balance, and then applies the algorithm to calculate the totals by these fields.

Algorithm of calculating totals by balance fields

For a query to be able to calculate the totals by the balance fields, the query should get both values of the balance for a period: the opening and closing balances. When a query gets only one of the two balances and this value is used to calculate the totals, the platform implicitly supplements the query by getting the field with the other balance.

The algorithm for calculating totals by balances consists of three steps:

1. calculation of totals for a grouping obtained before grouping by period;
2. calculation of totals for the grouping by period;
3. calculation of totals for a grouping obtained after grouping by period.

When totals are calculated for a grouping obtained before grouping by period, records are sorted by the first-level fields that have not been grouped yet and by the period field, which is followed by records iteration. The first records for a combination of the first-level fields are used to sum up the opening balance while the last records are used to calculate the closing balance.

When totals are calculated for the grouping by period, records are sorted by period, records for each date are iterated, and the sum is calculated for various combinations of the first-level field values (those that are not obtained using .(dot) from another field in a selection list) that have not yet been used to get a selection for the date that is equal to or greater than that calculated for the opening balance and less than or equal to the one calculated for the closing balance. Keep in mind that when a field from a linked table is selected for a query result, the balances for the period are calculated taking into account the combinations of values for this field.

When totals are calculated by a grouping inside a grouping by period, the calculation is performed in the same manner as for regular fields, by simply summing them up.

Using balance fields combined with other fields

If a balance field is used in an expression, for each opening balance the platform attempts to find a similar expression that contains a closing balance. If the expression is not found, the field is implicitly added to the query.

Example:

SELECT
    Warehouse AS Warehouse,
    Items AS Items,
    Period AS Period,
    QuantityOpeningBalance * Items.PurchasePrice AS QuantityOpeningBalance,
    QuantityTurnover * Items.PurchasePrice AS QuantityTurnover,
    QuantityClosingBalance * Items.PurchasePrice AS QuantityClosingBalance
FROM
    AccumulationRegister.ItemsAccounting.BalanceAndTurnovers(, , Week, , ) AS ItemsAccountingBalanceAndTurnovers
TOTALS
    SUM(QuantityOpeningBalance), SUM(QuantityTurnover), SUM(QuantityClosingBalance)
BY
    Warehouse,
    Period,
    Items

If an expression uses multiple opening or closing balance fields, the platform does not recognize this expression as a balance expression and calculates totals by this expression using simple summing up.

Calculating totals by recorder

If you need to get totals for balances by a register recorder, note that you can only get totals by the recorder within the Period grouping. This feature is due to the fact that a recorder is an elaboration of a period. If totals by a recorder are obtained before those by a period, the resulting totals are incorrect. A similar approach applies to get totals by row number: such totals can only be obtained within a grouping by period and recorder.

Totals by recorder attribute

It is impossible to calculate totals correctly by an attribute of a recorder. If you need to include balances with a specification of the recorder attribute in the results, you should create a grouping by recorder and only include the recorder attribute in the result, without including the recorder itself.

Next page: Catalog field contents