Aggregate functions of the data composition system. Aggregate functions of the data composition system Create a new report

Important! If a function parameter is of type String and it specifies a field name that contains spaces, then the field name must be enclosed in square brackets.
For example: "[Number of Turnover]".

1. Amount (Total)- calculates the sum of the values ​​of expressions passed to it as an argument for all detailed records. You can pass an Array as a parameter. In this case, the function will be applied to the contents of the array.

Example:
Amount(Sales.AmountTurnover)

2. Count - calculates the number of values ​​other than NULL. You can pass an Array as a parameter. In this case, the function will be applied to the contents of the array.

Syntax:
Quantity([Various] Parameter)

To indicate the receipt of different values, you should specify Distinct before the Quantity method parameter.

Example:
Quantity(Sales.Counterparty)
Quantity(Various Sales.Counterparty)

3. Maximum - gets the maximum value. You can pass an Array as a parameter. In this case, the function will be applied to the contents of the array.

Example:
Maximum(Remaining.Quantity)

4. Minimum - gets the minimum value. You can pass an Array as a parameter. In this case, the function will be applied to the contents of the array.

Example:
Minimum(Remaining.Quantity)

5. Average - Gets the average for non-NULL values. You can pass an Array as a parameter. In this case, the function will be applied to the contents of the array.

Example:
Average(Remaining.Quantity)

6. Array - forms an array containing the parameter value for each detailed record.

Syntax:
Array([Various] Expression)

You can use a table of values ​​as a parameter. In this case, the result of the function will be an array containing the values ​​of the first column of the value table passed as a parameter. If the expression contains an Array function, then it is assumed that this expression is aggregate. If the Various keyword is specified, the resulting array will not contain duplicate values.

Example:
Array(Counterparty)

7. ValueTable - generates a table of values ​​containing as many columns as there are parameters for the function. Detail records are obtained from data sets that are needed to obtain all the fields involved in function parameter expressions.

Syntax:
ValueTable([Various] Expression1 [AS ColumnName1][, Expression2 [AS ColumnName2],...])

If the function parameters are residual fields, then the resulting table of values ​​will contain values ​​for records for unique combinations of dimensions from other periods. In this case, values ​​are obtained only for balance fields, dimensions, accounts, period fields and their details. The values ​​of the remaining fields in records from other periods are considered equal to NULL. If an expression contains the ValueTable function, then this expression is considered to be an aggregate expression. If the keyword Various is specified, then the resulting table of values ​​will not contain rows containing the same data. After each parameter there can be an optional keyword AS and a name that will be assigned to the column of the value table.

Example:
Table of Values ​​(Various Nomenclature, Characteristics of Nomenclatures AS Characteristics)

8. Collapse (GroupBy) - designed to remove duplicates from an array.

Syntax:
Collapse(Expression, ColumnNumbers)

Parameters:

• Expression- an expression of the Array or ValueTable type, the values ​​of the elements of which need to be collapsed;
• Column Numbers- (if the expression is of the ValueTable type) type String. Numbers or names (separated by commas) of the columns of the value table, among which you need to look for duplicates. The default is all columns.

9. GetPart - gets a table of values ​​containing certain columns from the original table of values.

Syntax:
GetPart(Expression, ColumnNumbers)

Parameters:

• Expression- type Table of Values. A table of values ​​from which to obtain columns;
• Column Numbers- type String. Numbers or names (separated by commas) of the columns of the table of values ​​that need to be obtained.

Example:
GetPart(Collapse(ValueTable(PhoneNumber, Address) ,"PhoneNumber"),"PhoneNumber");

10. Order - designed to organize array elements and table of values.

Syntax:
Arrange(Expression, ColumnNumbers)

Parameters:

• Expression- Array or Value Table from which you need to get columns;
• Column Numbers- (if the expression is of the ValueTable type) numbers or names (separated by commas) of the columns of the value table by which you want to sort. May contain the ordering direction and the need for auto-ordering: Descending/Ascending + Auto-ordering.

Example:
Arrange(ValueTable(PhoneNumber, Address, CallDate),"CallDate Descending");

11. JoinStrings - designed to combine strings into one line.

Syntax:
ConnectRows(Value, ItemSeparator, ColumnSeparator)

Parameters:

• Meaning- expressions that need to be combined into one line. If it is an Array, then the elements of the array will be combined into a string. If it is a ValueTable, then all columns and rows of the table will be combined into a row;
• Element Separator- a string containing text to be used as a separator between array elements and value table rows. Default – line feed character;
• Column Separators- a line containing text that should be used as a separator between the columns of the value table. Default "; ".

12. GroupProcessing - returns the GroupProcessingDataCompositionData object. In the object's Data property, the grouping values ​​are placed in the form of a table of values ​​for each expression specified in the Expressions function parameter. When using hierarchical grouping, each level of the hierarchy is processed separately. Values ​​for hierarchical records are also placed in the data. The CurrentItem property of the object contains the value table row for which the function is currently being calculated.

Syntax:
GroupProcessing(Expressions, Hierarchy Expressions, GroupName)

Parameters:

• Expressions. Expressions to be evaluated. A line containing comma-separated expressions that need to be evaluated. After each expression there may be an optional keyword AS and the name of the column of the resulting table of values. Each expression forms a column in the table of values ​​of the Data property of the DataCompositionGroupProcessingData object.
• ExpressionsHierarchies. Expressions to evaluate for hierarchical records. Similar to the Expressions parameter, with the difference that the Hierarchy Expressions parameter is used for hierarchical records. If the parameter is not specified, the expressions specified in the Expression parameter are used to calculate values ​​for hierarchical records.
• GroupName. The name of the grouping in which to calculate the processing grouping. Line. If not specified, the calculation occurs in the current grouping. If the calculation is carried out in a table and the parameter contains an empty string or is not specified, then the value is calculated for the grouping - the string. The layout compositor, when generating a data layout layout, replaces this name with the name of the grouping in the resulting layout. If grouping is not available, then the function will be replaced with a NULL value.

Syntax:
Each(Expression)

Parameter :

• Expression- Boolean type.

14. Any (Any)- if at least one record has the value True, then the result is True, otherwise False

Syntax:
Any(Expression)

Parameter :

• Expression- Boolean type.

15. Standard Deviation of the General Population (Stddev_Pop) - calculates the standard deviation of the population. Calculated using the formula: SQRT(Variance of the General Population (X)).

Syntax:
StandardDeviation of the General Population(Expression)

Parameter :

• Expression- Number type.

Example:

16. Standard Deviation of Sample (Stddev_Samp) - calculates the cumulative sample standard deviation. Calculated using the formula: SQRT(Sample Variance(X)).

Syntax:
StandardDeviationSample(Expression)

Parameter :

• Expression- Number type.

Example:

17. VarianceSamples (Var_Samp) - calculates the typical difference of a series of numbers without taking into account the NULL values ​​in this set. Calculated using the formula: (Amount(X^2) - Amount(X)^2 / Quantity(X)) / (Quantity(X) - 1). If Quantity(X) = 1, then NULL is returned.

Syntax:
VarianceSamples(Expression)

Parameter :

• Expression- Number type.

19. Covariance of the General Population (Covar_Pop) - calculates the covariance of a number of numerical pairs. Calculated using the formula: (Sum(Y * X) - Sum(X) * Sum(Y) / n) / n, where n is the number of pairs (Y, X) in which neither Y nor X are NULL.

Syntax:
CovarianceofPopulation(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

20. CovarianceSamples (Covar_Samp) - calculates the typical difference of a series of numbers without taking into account the NULL values ​​in this set. Calculated using the formula: (Sum(Y * X) - Sum(Y) * Sum(X) / n) / (n-1), where n is the number of pairs (Y, X) in which neither Y nor X are NULL.

Syntax:
CovarianceSamples(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

21. Correlation (Corr) - calculates the correlation coefficient of a number of numerical pairs. Calculated using the formula: Covariance of the Population (Y, X) / (Standard Deviation of the Population (Y) * Standard Deviation of the Population (X)). Pairs in which Y or X are NULL are not taken into account.

Syntax:
Correlation(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

22. RegressionSlope (Regr_Slope) - calculates the slope of the line. Calculated using the formula: Covariance of the General Population (Y, X) / Variance of the General Population (X). Calculated without taking into account pairs containing NULL.

Syntax:
RegressionSlope(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

23. RegressionIntercept (Regr_Intercept) - calculates the Y-point of intersection of the regression line. Calculated using the formula: Mean(Y) - RegressionSlope(Y, X) * Mean(X). Calculated without taking into account pairs containing NULL.

Syntax:
RegressionSegment(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

25. RegressionR2 (Regr_R2) - calculates the coefficient of determination. Calculated without taking into account pairs containing NULL.

Syntax:
RegressionR2(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

• Null - if Variance of the General Population (X) = 0;
• 1 - if Variance of the General Population (Y) = 0 AND Variance of the General Population (X)<>0;
• POW(Correlation(Y,X),2) - if the Variance of the General Population(Y)>0 AND the Variance of the General Population(X)<>0.

26. RegressionAverageX (Regr_AvgX) - calculates the average of X after eliminating X and Y pairs where either X or Y is empty. Average(X) is calculated without taking into account pairs containing NULL.

Syntax:
RegressionAverageX(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

27. RegressionAverageY (Regr_AvgY) - calculates the average of Y after eliminating X and Y pairs where either X or Y is empty. Average(Y) is calculated without taking into account pairs containing NULL.

Syntax:
RegressionAverageY(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

28. RegressionSXX (Regr_SXX) - is calculated using the formula: RegressionQuantity(Y, X) * Dispersion of the General Population(X). Calculated without taking into account pairs containing NULL.

Syntax:
RegressionSXX(Y, X)

Parameters:

• Y- type Number;
• X- Number type.

Example:
SELECT RegressionSYY(Y, X) FROM Table
Result: 6445.55556

30. RegressionSXY (Regr_SXY) - calculated using the formula: RegressionQuantity(Y, X) * Covariance of the General Population(Y, X). Calculated without taking into account pairs containing NULL.

Syntax:
RegressionSXY(Y,X)

Parameters:

• Y- type Number;
• X- Number type.

31. Rank

Syntax:
PlaceInOrder(Order, HierarchyOrder, GroupName)

Parameters:

• Order– type String. Contains expressions in the sequence of which you want to arrange group records, separated by commas. The ordering direction is controlled using the words Ascending, Descending. You can also follow the field with Auto Order, which means that when you order links, you must use the order fields defined for the referenced object. If no sequence is specified, then the value is calculated in the grouping sequence;
• OrderHierarchy– type String. Contains ordering expressions for hierarchical records;
• GroupName– type String. The name of the grouping in which to calculate the processing grouping. If not specified, the calculation occurs in the current grouping. If the calculation is carried out in a table and the parameter contains an empty string or is not specified, then the value is calculated for the grouping - the string. The layout composer, when generating a data layout layout, replaces this name with the name of the grouping in the resulting layout. If grouping is not available, then the function will be replaced with a NULL value.

Example:
PlaceInOrder("[Number of Turnover]")

32. ClassificationABC

Syntax:
ClassificationABC(Value, Number of Groups, PercentageForGroups, GroupName)

Parameters:

• Meaning– type String. by which the classification must be calculated. A line containing the expression;
• Number of Groups- Number type. Specifies the number of groups to be divided into;
• PercentageForGroups- type String. As many as minus 1 needs to be divided into groups. Separated by commas. If not specified, then automatically;
• GroupName- type String. The name of the grouping in which to calculate the processing grouping. If not specified, the calculation occurs in the current grouping. If the calculation is carried out in a table and the parameter contains an empty string or is not specified, then the value is calculated for the grouping - the string. The layout composer, when generating a data layout layout, replaces this name with the name of the grouping in the resulting layout. If grouping is not available, then the function will be replaced with a NULL value.

Example:
ClassificationABC("Amount(GrossProfit)", 3, "60, 90")

The article describes an example practical use weighted average value in the report results. Some techniques for working with access control systems are shown. The article is intended for a trained reader with at least basic skills in working with access control systems and query builder.

The calculation of the weighted average is actively used in tasks related to management accounting and other business calculations.

By definition, - WEIGHTED AVERAGE(weighted average) is an arithmetic average that takes into account the weight of each of the terms for which this average is calculated.

In almost all textbooks on management accounting, to illustrate the weighted average, they give an example with the purchase of three batches of the same product - each batch of goods has a different purchase price and a different quantity. It is clear that if in such a situation we take the arithmetic average of the purchase prices, we will get the average temperature in the hospital - a figure that has no practical meaning. In such a situation, it is the weighted average that makes sense.

The same textbook example: goods were purchased in three lots, one of which was 100 tons for 70 pounds. Art. per ton, the other - 300 tons at 80 pounds. Art. per ton and third - 50 tons at 95 lbs. Art. per ton, then in total he purchases 450 tons of goods; regular average price purchases will be (70 + 80 + 95) : 3 = 81.7 f. Art. The weighted average price, taking into account the volumes of each batch, is equal to (100 × 70) + (300 × 80) + (50 × 95) : 450 = 79.4 pounds. Art. per ton.

Formula:

Where X are the values ​​whose weighted average we want to obtain, and W are the weights.

This is where the theory ends.

I had to deal with this when I was displaying data on sales of goods in a report, grouped by managers, where it was necessary to obtain profitability as a result. The lines of the report displayed the profitability for each product sold; in the results, it was necessary to see what profitability the manager worked with. Accordingly, profitability is a “value”, and the “weight” of this value is revenue. A number of clarifications to complete the picture. Revenue (sales volume) is the product’s selling price and quantity multiplied. Gross profit is revenue minus cost (how exactly cost was calculated in the context of this article is not important). And finally, our profitability is the ratio of gross profit to revenue expressed as a percentage.

The question arises: what profitability did the manager perform in the reporting period? To answer this question correctly, you need to calculate the weighted average profitability value.

In order to obtain the profitability-weighted average in the SKD, in the query constructor we create an auxiliary field of the formarbitrary expression, where we record the product of profitability and revenue. We assign an alias to this field -AuxiliaryField. See the picture below.

We will not display this field in the report; we need the data to calculate the results. We will calculate the results in the ACS tabResources.

Another technique for working with ACS on the “Resources” tab, where the calculation of totals is indicated, is the ability to use expressions in the “Expression” field with data from the “Available Fields” field. See the picture below.

For the report column “Percentage of Profitability” we write the expression Amount(AuxiliaryField)/Amount(Revenue).

Let's summarize, first of all, it is important to understand what a weighted average is, and where you need to use just the arithmetic average, and where - the weighted average. From a technical point of view, two points may present some difficulty - the creation of an auxiliary field in the report, and the ability to use an arbitrary expression in the ACS resources to calculate the results we need.

I hope that this article will be useful for someone.

Let's look at an example:

Several units of goods are shipped to the client at different discounts. You need to display the discount values ​​for each product in a report and calculate the average discount. Let's create a report on the access control system.
For all fields for which we need to display totals, we will set an expression (method) for their calculation on the “Resources” tab. For the "Discount" Field, set the calculation method as "Average".

We output the report:

As you can see, the total for the “Discount” field is calculated as the arithmetic average of the field values, i.e. the sum of all values ​​divided by the number of values. But because Since the volumes of goods shipped with different discounts are different, the average discount can be calculated in another way: for example, as the ratio of the total amount of goods shipped, taking into account the discount, to the total amount of goods shipped. But here a question arises: if you can write a formula to calculate the values ​​of the fields of detailed records in the ACS, then the totals are calculated automatically according to the expression specified on the “Resources” tab, and there, as we saw earlier, “Average” is calculated as the arithmetic mean of the displayed values, as In this case, should I calculate the total in this column using my own formula?
It's actually not difficult. To do this, we will adjust our report by adding to it the necessary fields that will participate in the calculation, in our case these are “AmountWithDiscount” and “AmountAtPrice”.

In order to “teach” the ACS to display our results, we take the following steps:
1. on the "Settings" tab, give the grouping "" a name, for example "TotalSKD", for this we call context menu for grouping and click “assign a name”;

2. on the “Layouts” tab, add a “grouping header layout”, where we select the previously assigned name “TotalSKD”;

3. we draw a line of the layout, where we leave all the results, the calculation of which suits us, as they are, and to calculate the average discount percentage we write a formula for calculation;

If everything is correct, then when the report is displayed, two total lines will be displayed below it, first the one that is generated automatically, then the one created by you.

In order to leave the output of only your total line, you need to on the "Settings" tab in the "Other settings" section of the layout output, disable the output for "Horizontal grand total layout" and "Vertical grand total layout"

In data composition setting up totals looks a little different than in requests. Let us define the “Query” data set in the data composition system.

In the request itself, we do not configure the totals, but go to the “Resources” tab of the data composition. At the data composition schema level, we determine resources. These are the fields that need to be counted at the grouping level. Click on the “>>” button and the system itself will transfer all the numeric fields and define them as resources.

You can also specify non-numeric fields in resources. For example, if you select the “Link” attribute, the system will count the number of documents in our groups. This information may also be useful. So, in the layout diagram we only define resources, and the groupings themselves are configured at the report variant level. The user can also create the groupings that he wants to see in his report option settings.

Let's create standard setting data composition.
Click on the “Open settings designer” button.

Let's select the report type - list. Click the “Next” button.

Let's select all fields and move the counterparty field to the top position. Click the “Next” button.

Let's select all fields and move the counterparty field to the top position. Click the “OK” button.

The resulting setting was:

As you can see, in setting up a report option, resources are highlighted with a green icon so that they can be quickly distinguished from other fields.

If we open our report in 1C:Enterprise mode and generate it, we will see that final data is generated at the grouping level. Results by item and by counterparties.

Setting up resources in the 1C data composition scheme

Now let's turn our attention to settings that exist for resources. In the “Expression” field we can specify an aggregate function that can be used to obtain the value of the resource. In the drop-down list you can see a number of standard functions, but not all. For example, there are no functions.

Here in the “Expression” field we can write our own expression.

In the "Expression" field we can also access the functions of common modules.

In addition, you can specify in the “Calculate by...” field which groupings the resource should be calculated by. If the “Calculate by...” field is not filled in, then the final value of the resource will be calculated at all grouping levels that are defined in the settings of the report option. In our case, we need to fill in the “Calculate by...” field of the “Quantity” resource, since we can sell the product with different units measurements. For example: oil in liters and wheels in pieces. Isn't it true that it would be illogical to add up the quantities of these goods? Therefore, we need to leave the summation of quantity at the item level, and at the counterparty level
Let's remove the summation.

If we generate a report, we will see that the quantity totals are calculated only by item, and the quantity totals for contractors are empty.

Possibilities when describing resources in the 1C data composition scheme

Let's take a look a number of non-obvious features related to the description of resources.

• Each resource can define several times. But this only makes sense if
  the resource will be calculated at different levels of groupings. For example, if the quantity, in one case
  is summed up for the item, and for contractors we get the minimum value.

If we generate a report, we will see that for the counterparty “Deriya” the minimum purchase is five units of the “Self-adhesive paper” product range.

• In the “Expression” field, in addition to writing a formula, you can use a special data composition function called “Calculate”. This function allows you to calculate some final value based on a certain formula. For example, for each counterparty it is necessary to know the percentage of purchases in natural units relative to the total volume. But how to get the total volume of purchases by quantity? To do this, use the “Calculate” function and write the following expression in the “Expression” field:

As you can see, all parameters of the "Calculate" function are strings. In order for the quantity field to be displayed beautifully in the report, we’ll configure it on the “Data Sets” tab. In the quantity line we will find the “Editing Options” field. Let’s open the dialog, find the “Format” line and edit the format line in it, setting the “Precision” value to two on the “Number” tab.

Let's run the report and see the result of calculating the percentage of purchases for the counterparty "AUPP KOS TOO" relative to
total volume:

At the end of the article I want to recommend you a free one from Anatoly Sotnikov. This is a course from an experienced programmer. It will show you on a separate basis how to build reports in the access control system. You just need to listen carefully and remember! You will receive answers to the following questions:

• How to create a simple list report?
• What are the Field, Path and Title columns on the “Fields” tab for?
• What are the limitations for layout fields?
• How to configure roles correctly?
• What are the roles for layout fields?
• Where can I find the data composition tab in a query?
• How to configure parameters in the access control system?
• It gets even more interesting...

Perhaps you shouldn’t try to surf the Internet yourself in search of the necessary information? Moreover, everything is ready for use. Just get started! All the details about what is in the free video lessons

Here is one of the lessons about bookmarking the data composition in a query: