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.
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:
- 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 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:
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
Every(Expression)
Every()
Any(Expression)
Any()
Stddev_Pop(Expression)
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(Expression)
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
If Count(X) = 1, NULL is retrieved.
Var_Samp(Expression)
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(Expression)
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(Y, X)
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(Y, X)
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(Y, X)
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(Y, X)
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(Y, X)
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(Y, X)
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(Y, X)
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
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(Y, X)
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(Y, X)
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 (Y, X)
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 (Y, X)
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 (Y, X)
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(Order, HierarchyOrder, GroupName)
Rank("[Quantity Turnover]")
ClassificationABC(Value, GroupCount, PercentageForGroups, GroupName)
ClassificationABC("Total(Quantity
Turnover)", 3, "60, 90")
See also:
Data
Composition System Expression Language
Bilingual
Presentation of Data Composition System Keywords