Aggregate Functions

Total
Count
VariousCount
Maximum
Minimum
Average
Array
ValueTable
GroupBy
GetPart
Order
JoinStrings
GroupProcessing
Every
Any
Stddev_Pop
Stddev_Samp
Var_Samp
Var_Pop
Covar_Pop
Covar_Samp
Corr
Regr_Slope
Regr_Intercept
Regr_Count
Regr_R2
Regr_AvgX
Regr_AvgY
Regr_SXX
Regr_SYY
Regr_SXY
Rank
ClassificationABC

Aggregate functions perform certain actions on a set of data.

Important! If function option is String and specifies field name with spaces, such name should be enclosed in square brackets.
Example: "[Quantity Turnover]".

Total

The Total aggregate function calculates a total of expression values passed as an argument for all detailed records. You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Total(Sales.SumTurnover)

Count

The Count function calculates the number of non-NULL values. You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Count(Sales.Contractor)

VariousCount

This function calculates the number of different values. In order to get different values, before an option of the Count method specify (Distinct). You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Count(Distinct Sales.Contractor)

Maximum

This function gets the maximum value. You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Maximum(Balance.Quantity)

Minimum

This function gets the minimum value. You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Minimum(Balances.Quantity)

Average

This function gets the mean value for non-NULL values. You can pass Array as a parameter. In this case the function will be applied to the array content.

Example:

Average(Balances.Quantity)

Array

This function creates an array that includes a parameter value for each detailed record. 

Syntax:

Array([Distinct] Expression)

You can use a value table as a parameter. Note that in this case the function results in an array containing the values of the first column of the value table passed as a parameter.
If an expression includes the Array function, this expression is considered to be an aggregate expression.
If the Distinct keyword is specified, the retrieved array will not include duplicate values.

Example:

Array(Contractor)

ValueTable

The function generates a table of values with the number of columns equal to the number of function parameters. Detailed records are retrieved from data sets that are required to retrieve all the fields that are used in function parameter expressions. 

Syntax:

ValueTable([Distinct] Expression1 [AS ColumnName1][, Expression2 [AS ColumnName2],...])

If fields of residual fields are used as function parameters, the resulting value table will include values for the entries by unique measurement combinations from other periods. Note that values are only obtained for residual fields, measurements, accounts, period fields and their attributes. Values of other fields in the entries from other periods are considered NULL.
If an expression includes the ValueTable function, this expression is considered to be an aggregate expression.
If the Distinct keyword is specified, the retrieved value table will not include rows with duplicate data.
Every parameter may be followed by the optional AS keyword and a name that will be applied to a column of the value table.

Example:

ValueTable(Distinct Products, ProductCharacteristic AS Characteristic)

GroupBy

This function removes duplicates from an array.

Syntax:

GroupBy(Expression, ColumnNumbers)

Parameters:

  • Expression - Array or ValueTable type expression that should have its item values grouped.
  • ColumnNumbers - (if the expression is ValueTable) String type. Numbers or names (comma-separated) of the value table columns where duplicates should be searched for. By default this includes all columns.

Example:

GroupBy(ValueTable(PhoneNumber, Address) ,"PhoneNumber").

GetPart

The function gets a value table that contains specific columns of the original value table.

Syntax:

GetPart(Expression, ColumnNumbers)

Parameters:

  • Expression - ValueTable type. A table of values the columns should be retrieved from.
  • ColumnNumbers - String type. Numbers or names (comma separated) of the value table columns that should be retrieved.

Returned value: ValueTable with only those columns that are specified in the parameter.

Example:

GetPart(GroupBy(ValueTable(PhoneNumber, Address) ,"PhoneNumber"),"PhoneNumber").

Order

Intended to order items of an array and value table.

Syntax:

Order(Expression, ColumnNumbers)

Parameters:

  • Expression - Array or ValueTable the columns should be retrieved from.
  • ColumnNumbers - (if the expression is of ValueTable type) numbers or names (comma separated) of the value table columns that should be used to order by. It may include the order direction and specify whether automatic ordering is required: Desc/Asc + AutoOrder.

Returned value: Array or ValueTable with ordered items.

Example:

Order(ValueTable(PhoneNumber, Address, CallDate),"CallDate Desc").

JoinStrings

Intended to join multiple strings into a single string.

Syntax:

JoinStrings (Value, ItemSeparator, ColumnSeparators)

Parameters:

  • Value - the expressions that should be joined into a single string. If it is an Array, array items will be joined into a string. If it is a ValueTable, all the table columns and rows will be joined into a string.
  • ItemSeparator - a string with a text that should be used as a separator between array items and rows of a value table. By default, it is a line feed character.
  • ColumnSeparators - a string with a text that should be used as a separator between columns of a value table. By default it is "; ".

Example:

JoinStrings(ValueTable(PhoneNumber, Address)).

GroupProcessing

Returns the DataCompositionGroupProcessingData object. In the Data property the object will have a value table with the grouping values for every expression specified in the parameter of the Expressions function. If hierarchical grouping is used, each hierarchy level is processed separately. Values of hierarchical records are also included in data.
The CurrentItem property of the object will hold the row of the value table the function is currently calculated for.

Syntax:

GroupProcessing(Expressions, HierarchyExpressions, GroupName)

Parameters:

  • Expressions. The expressions to be calculated. The string listing comma separated expressions to be calculated. Each expression may be followed by the optional AS keyword and a column name of the resulting value table. Each expression generates a column of the value table for the Data property of the DataCompositionGroupProcessingData object.
  • HierarchyExpressions. The expressions to be calculated for hierarchical records. Similar to the Expressions parameter except that the HierarchyExpressions parameter is used for hierarchical records. If the parameter is omitted, the expressions specified in the Expression parameter are used to calculate the values for hierarchical records.
  • GroupName. Name of the group to calculate processing grouping in. String. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.

Every

If any record is False, the result is False. Or True.

Syntax:

Every(Expression)

Parameter:

  • Expression - Boolean type. 

Example:

Every()

Any

If any record is True, the result is True. Or False

Syntax:

Any(Expression)

Parameter:

  • Expression - Boolean type. 

Example:

Any()

Stddev_Pop

Standard deviation for an aggregate is calculated. The following formula is used: SQRT(Var_Pop(X))

Syntax:

Stddev_Pop(Expression)

Parameter:

  • Expression - Number type. 

Retrieved value type: Number.

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Var_Samp(Y, X) FROM Table

Result: 805.694444

Stddev_Samp

Calculates total type standard deviation. The following formula is used: SQRT(Var_Samp(X))

Syntax:

Stddev_Samp(Expression)

Parameter:

  • Expression - Number type. 

Retrieved value type: Number.

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT SelectionStdDeviation(Y) FROM Table

Result: 28.3847573

Var_Samp

Calculates typical variation of a number sequence without taking NULL values into account in this set. The following formula is used: (Total(X^2) - Total(X)^2 / Count(X)) / (Count(X) - 1)
        If Count(X) = 1, NULL is retrieved.

Syntax:

Var_Samp(Expression)

Parameter:

  • Expression - Number type. 

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Var_Samp(Y, X) FROM Table

Result: 805.694444

Var_Pop

Calculates variation of a number sequence aggregate without taking NULL values into account in this set. The following formula is used: (Total(X ^2) - Total(X)^2 / Count(X)) / Count(X)

Syntax:

Var_Pop(Expression)

Parameter:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Var_Pop(Y, X) FROM Table

Result: 716.17284

Covar_Pop 

Calculates covariation of a range of number pairs. The following formula is used: (Total(Y * X) - Total(X) * Total(Y) / n) / n, when n is the number of pairs (Y, X) where neither Y nor X are NULL.

Syntax:

Covar_Pop(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Covar_Pop(Y, X) FROM Table

Result: 59.4444444

Covar_Samp

Calculates typical variation of a number sequence without taking NULL values into account in this set. The following formula is used: (Total(Y * X) - Total(Y) * Total(X) / n) / (n-1), where n is the number of pairs (Y, X) where neither Y nor X is NULL

Syntax:

Covar_Samp(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Covar_Samp(Y, X) FROM Table

Result: 66.875

Corr

Calculates correlation index of a range of number pairs. The following formula is used: Covar_Pop(Y, X) / (Stddev_Pop(Y) * Stddev_Pop(X)) The pairs where Y or X is NULL.

Syntax:

Corr(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Corr(X, Y) FROM Table

Result: 0.860296149

Regr_Slope

Calculates line slope. The following formula is used: Covar_Pop(Y, X) / Var_Pop(X). Calculated without the pairs with NULL.

Syntax:

Regr_Slope(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_Slope(Y, X) FROM Table

Result: 8.91666667

Regr_Intercept

Calculates Y cross point of regression line. The following formula is used: Average(Y) - Regr_Slope(Y, X) * Average(X). Calculated without the pairs with NULL.

Syntax:

Regr_Intercept(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_Intercept(Y, X) FROM Table

Result: -20.361111

Regr_Count

Calculates the number of pairs without NULL.

Syntax:

Regr_Count(Y, X)

Parameters:

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_Count(Y, X) FROM Table

Result: 9

Regr_R2

Calculates coefficient of determination. Calculated without the pairs with NULL.

Syntax:

Regr_R2(Y, X)

Parameters:

Null if Var_Pop(X) = 0;

1 if Var_Pop(Y)=0 AND Var_Pop(X)<>0;

POW(Corr(Y,X),2) - if Var_Pop(Y)>0 AND Var_Pop(X)<>0

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_R2(Y, X) FROM Table

Result: 0.740109464

Regr_AvgX

Calculates average X after excluding pairs of X and Y where either X or Y is blank. Average(X) is calculated without the pairs with NULL.

Syntax:

Regr_AvgX(Y, X)

Parameters:

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

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_AvgX(Y, X) FROM Table

Result: 5

Regr_AvgY

Calculates average Y after excluding pairs of X and Y where either X or Y is blank. Average(Y) is calculated without the pairs with NULL.

Syntax:

Regr_AvgY(Y, X)

Parameters:

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

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_AvgY(Y, X) FROM Table

Result: 24.2222222

Regr_SXX

The following formula is used: Regr_Count(Y, X) * Var_Pop(X). Calculated without the pairs with NULL.

Syntax:

Regr_SXX (Y, X)

Parameters:

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

Retrieves the total of square roots of independent expressions used in regression linear model. The function may be used to evaluate statistical validity of regression model.

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_SXX(Y, X) FROM Table

Result: 60

Regr_SYY

The following formula is used: Regr_Count(Y, X) * Var_Pop(Y). Calculated without the pairs with NULL.

Syntax:

Regr_SYY (Y, X)

Parameters:

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

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_SYY(Y, X) FROM Table

Result: 6445.55556

Regr_SXY

The following formula is used: Regr_Count(Y, X) * Covar_Pop(Y, X) Calculated without the pairs with NULL.

Syntax:

Regr_SXY (Y, X)

Parameters:

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

Example:

Table:

X Y
1 7
2 1
3 2
4 5
5 7
6 34
7 32
8 43
9 87

SELECT Regr_SXY(Y, X) FROM Table

Result: 535

Rank

Syntax:

Rank(Order, HierarchyOrder, GroupName)

Parameters:

  • Order - String type. Contains statements that should have their comma separated group records ordered in their sequence. The ordering direction is managed by Asc, Desc words. The field may also be followed by AutoOrder string which means that ordering fields specified for the referenced object should be used for reference ordering. If the sequence is not specified, the value is calculated in the grouping sequence;
  • HierarchyOrder - String type. Contains ordering statements for hierarchical records;
  • GroupName - String type. Name of the group to calculate processing grouping in. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.

If a sequence includes multiple records with identical values of ordering fields, the function will return identical values for all such records.

Example:

Rank("[Quantity Turnover]")

ClassificationABC

Syntax:

ClassificationABC(Value, GroupCount, PercentageForGroups, GroupName)

Parameters:

  • Value - String type.  Used to calculate classification by. String that specifies the expression;
  • GroupCount - Number type; Specifies the number of groups to divide into;
  • PercentageForGroups - String type; The number of groups to divide into minus 1. Comma separated. If omitted, automatically;
  • GroupName - String type. Name of the group to calculate processing grouping in. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.

The function results in class number beginning with 1 which corresponds to class A.

Example:

ClassificationABC("Total(Quantity Turnover)", 3, "60, 90")

 

See also:
                      
Data Composition System Expression Language
                       Bilingual Presentation of Data Composition System Keywords

1C:Enterprise Developer's Community