Aggregate functions of the data composition system. Aggregate functions of the data composition system Changing the order of columns
In this short note I want to show how you can summarize values at various levels of grouping in a report using a data composition system.
As shown in the image, only at the “Item Groups” grouping level, the “Order” resource is calculated, it displays how much needs to be ordered for the current item group based on certain conditions:
This value can only be calculated at this grouping level, since there are no values above or below to calculate. For example, at the level of detailed records, there is no data on the maximum quantity in a group, because this data is valid only for the group as a whole, and not for its individual components.
Accordingly, now it is necessary to calculate the totals for the above groupings (“Warehouses”, “Warehouse Types”) and the overall total.
To do this, use the function CalculateExpressionWithGroupArray:
EVALUATE EXPRESSIONWITHGROUPARRAY (EVALEXPRESSIONWITHGROUPARRAY)
Syntax:
EvaluateExpressionWithGroupArray(,)
Description:
The function returns an array, each element of which contains the result of evaluating an expression for grouping by the specified field.
The layout compositor, when generating a layout, converts function parameters into terms of data composition layout fields. For example, the Account field will be converted to DataSet.Account.
The layout builder, when generating expressions for the output of a custom field whose expression contains only the CalculateArrayWithGroupArray() function, generates the output expression so that the output information is ordered. For example, for a custom field with the expression:
CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty")
The layout builder will generate the following expression for output:
ConnectRows(Array(Order(CalculateExpressionWithGroupingValueTable("View(Sum(DataSet.AmountTurnover)),Sum(DataSet.AmountTurnover)",,"DataSet.Account"),"2")))
Options:
Type: String. The expression to be evaluated. String, for example, Amount(AmountTurnover).
Type: String. Grouping field expressions – expressions of grouping fields, separated by commas. For example, Contractor, Party.
Type: String. An expression describing the selection applied to detail records. The expression does not support the use of aggregate functions. For example,DeletionFlag = False.
Type: String. An expression describing the selection applied to group records. For example, Amount(AmountTurnover) > &Parameter1.
Example:
Maximum(CalculateExpressionWithGroupArray("Amount(AmountTurnover)", "Counterparty"));
A detailed description of the function syntax can be found at http://its.1c.ru/db/v837doc#bookmark:dev:TI000000582
Now, for the calculation, we duplicate the “Order” field, with different values “Calculate by...”, using the following expressions, note that in each higher level the values of the levels below the groupings are used.
As a result, we get the following construction:
Login to the site as a student
Log in as a student to access school materials
Data composition system 1C 8.3 for beginners: counting the results (resources)
The purpose of this lesson will be:
- Write a report that displays a list of products (Food directory), their calorie content and taste.
- Group products by color.
- Learn about summarizing (resources) and calculated fields.
Create a new report
As in previous lessons, we open the database " Deli"in the configurator and create a new report through the menu" File"->"New...":
Document type - external report:
In the report setup form, write the name " Lesson3" and press the button " Open data composition diagram":
Leave the default schema name and click the " Ready":
Adding a request through the constructor
On the tab " Data set" click green plus sign and select " Add Dataset - Query":
Instead of writing the request text manually, we run it again query constructor:
On the "tab" Tables"drag the table" Food" from the first column to the second:
Select from the table " Food"fields that we will request. To do this, drag and drop the fields" Name", "Taste", "Color" And " Calorie content" from the second column to the third:
It turned out like this:
Press the button " OK" - the request text was generated automatically:
Creating report presentation settings
Go to the tab " Settings" and click on magic wand, to call settings designer:
Select the report type " List..." and press the button " Further":
Drag from the left column to the right the fields that will be displayed in the list and click " Further":
Drag from the left column to the right field " Color" - it will happen grouping lines in the report. Click " OK":
And here is the result of the designer’s work. Hierarchy of our report:
- the report as a whole
- grouping "Color"
- detailed entries - lines with food names
Save the report (button diskette) And without closing We will immediately open the configurator in user mode. It turned out like this:
Changing the order of columns
But let's let's change the order columns (up and down arrows) so that it looks like the figure below:
Let's save the report and open it again in user mode:
Great, that's much better.
Let's sum up the calorie content
It would be nice to summarize the calorie content of foods by group. To see the sum of the calorie content of all products, say, white or yellow. Or find out the total calorie content of all products in the database.
For this purpose, there is a mechanism for calculating resources.
Go to the tab " Resources"and drag the field" Calorie content"(we are going to summarize it) from the left column to the right.
In this case, in the field, select the expression from the drop-down list " Amount(Calorie)", since the total will be the sum of all elements included in the total:
We save and generate a report:
We now have results for each of the groups and for the report as a whole.
Let's sum it up (average) in terms of calories
Now let's make it appear in another column average calorie content of products by groups and in the report as a whole.
You cannot touch the existing “Calorie” column - the total is already displayed in it, so let's create another field, which will be an exact copy of the "Calorie" field.
To create such a “virtual” field, we will use the mechanism calculated fields.
Go to the tab " Calculated fields" and press green plus sign:
In a collumn " Data path"we write the name of the new field ( seamlessly, without spaces). Let it be called " Average Calorie Content", and in the column " Expression"we write the name of an existing field on the basis of which the new field will be calculated. We write there " Calorie content". Column " Heading" will be filled in automatically.
We have added a new field (" Average Calorie Content"), but it will not appear in the report by itself - you need to either call again settings designer("magic wand") or add this field manually.
Let's do it second way. To do this, go to the tab " Settings", select " Report"(after all, we want to add the field as a whole to the report), select the tab at the bottom" Selected fields"and drag the field" Average Calorie Content"from the left column to the right:
It turned out like this:
We save and generate a report:
The field has appeared and we see that its values are the values of the "Calorie" field. Great!
To do this, we will again use the mechanism already familiar to us resources(summarizing). Go to the tab " Resources"and drag the field" Average Calorie Content"from the left column to the right:
Moreover, in the column " Expression"choose" Average(AverageCalorie)":
We save and generate a report:
We see that for the groups, that is, for each color, and for the report as a whole, the average value was calculated absolutely correctly. But they are present extra entries for individual products (not groups) that I would like to remove from the report.
Do you know why they appeared (values not by group)? Because when we added the field " Average Calorie Content"in the report settings, in the second step we selected the entire report and this new field ended up in the element " Detailed records".
Let's fix the error. To do this, go back to the tab " Settings", select " Detailed entries" first from above (step 2) and then " Detailed entries"from below (step 3), go to the bookmark" Selected fields" and we will see in its right column the element " Auto".
Element " Auto" - this is not one field. These are several fields that fall here automatically based on higher-level settings.
To see what these fields are, click on the element " Auto" right button and select " Expand":
Element " Auto" expanded into the following fields:
And here is our field" Average Calorie Content"which came here from the point " Report" when we dragged him there. Just let's remove check the box next to this field to remove its output.
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
- receives 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 table of values, 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)
Options :
- 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.
Collapse(ValueTable(PhoneNumber, Address) ,"PhoneNumber");
9. GetPart - gets a table of values containing certain columns from the original table of values.
Syntax:
GetPart(Expression, ColumnNumbers)
Options :
- 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)
Options :
- Expression- Array or ValueTable 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)
Options :
- 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 table of values. Default "; ".
ConnectRows(ValueTable(PhoneNumber, Address));
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)
Options :
- 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 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.
Syntax:
Each(Expression)
Parameter :
- Expression- Boolean type.
Every()
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.
Any()
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:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 805.694444
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:
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 28.3847573
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.
SELECT Variance of the Population (Y) FROM Table
Result: 716.17284
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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 59.4444444
20. CovarianceSample (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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 66.875
21. Correlation (Corr) - calculates the correlation coefficient of a number of numerical pairs. It is 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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 0.860296149
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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 8.91666667
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)
Options :
- Y- type Number;
- X- Number type.
SELECT RegressionCount(Y, X) FROM Table
Result: 9
25. RegressionR2 (Regr_R2) - calculates the coefficient of determination. Calculated without taking into account pairs containing NULL.
Syntax:
RegressionR2(Y, X)
Options :
- 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.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 0.740109464
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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 5
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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 24.2222222
28. RegressionSXX (Regr_SXX) - 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)
Options :
- Y- type Number;
- X- Number type.
Example:
SELECT RegressionSYY(Y, X) FROM Table
Result: 6445.55556
30. RegressionSXY (Regr_SXY) - is 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)
Options :
- Y- type Number;
- X- Number type.
| X | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| Y | 7 | 1 | 2 | 5 | 7 | 34 | 32 | 43 | 87 |
Result: 535
31. Rank
Syntax:
PlaceInOrder(Order, HierarchyOrder, GroupName)
Options :
- 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 ordering links, you must use the ordering 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 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, the function will be replaced with a NULL value.
Example:
PlaceInOrder("[Number of Turnover]")
32. ClassificationABC
Syntax:
ClassificationABC(Value, Number of Groups, PercentageForGroups, GroupName)
Options :
- 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 groups as minus 1 needs to be divided into. 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 of the practical use of a weighted average in 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.
A-priory, - 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 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; the usual average purchase price will be (70 + 80 + 95) : 3 = 81.7 pounds. 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).
To 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 you need to use 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.
