Difference between revisions of "Calculate Column Aggregate"
Line 5: | Line 5: | ||
The Column Aggregate Node performs the following statistical functions on a column of data in the [[Data Table]]: | The Column Aggregate Node performs the following statistical functions on a column of data in the [[Data Table]]: | ||
− | * | + | *Mean |
*Sum | *Sum | ||
*Minimum | *Minimum | ||
*Maximum | *Maximum | ||
*Count | *Count | ||
+ | *Median <sup>1</sup> | ||
+ | *Standard Deviation <sup>1</sup> | ||
+ | |||
+ | <sup>1</sup> Features available in Presence 3.6 and later. | ||
To include this Node in your Task drop a "Calculate Column Aggregate" Task Element into your Task design from the Data Access Task Elements list, after which you will be presented with the following dialog box: | To include this Node in your Task drop a "Calculate Column Aggregate" Task Element into your Task design from the Data Access Task Elements list, after which you will be presented with the following dialog box: | ||
Line 30: | Line 34: | ||
Example: The average of 10,15,12,11,19,7,18 is 13.142857 | Example: The average of 10,15,12,11,19,7,18 is 13.142857 | ||
+ | |||
+ | === Median === | ||
+ | |||
+ | This is the middle number if all numbers are arranged in ascending order (assuming an odd number of values). If there is an even number of values the mean is taken of the two middle values. | ||
+ | |||
+ | Example: The median of 10,15,12,11,19,7,18 is 11 | ||
+ | |||
+ | === Standard Deviation === | ||
+ | |||
+ | Calculates the square root of the variance from the mean. This is achieved as follows: | ||
+ | |||
+ | # Calculate the mean | ||
+ | # For each value calculate the difference between that value and the mean | ||
+ | # Square that number | ||
+ | # Add them all together | ||
+ | # Divide by total number of values | ||
+ | # Take square root | ||
+ | |||
+ | Example: The standard deviation of 2,4,6,8,10 is 2.8243 | ||
+ | |||
+ | Which equals (where m = mean, or 6.0): | ||
+ | |||
+ | : (2-m)<sup>2</sup> = -4<sup>2</sup> = 16 | ||
+ | + (4-m)<sup>2</sup> = -2<sup>2</sup> = 4 | ||
+ | + (6-m><sup>2</sup> = 0<sup>2</sup> = 0 | ||
+ | + (8-m)<sup>2</sup> = 2<sup>2</sup> = 4 | ||
+ | + (10-m)<sup>2</sup> = 4<sup>2</sup> = 16 | ||
+ | |||
+ | = 40 | ||
+ | |||
+ | 40 / 5 = 8 | ||
+ | |||
+ | Square root of 8 = 2.8243 | ||
=== Sum === | === Sum === |
Revision as of 08:42, 20 July 2010
Contents
Calculate Column Aggregate
This document has been revised for Presence version 3.6.
The Column Aggregate Node performs the following statistical functions on a column of data in the Data Table:
- Mean
- Sum
- Minimum
- Maximum
- Count
- Median ^{1}
- Standard Deviation ^{1}
^{1} Features available in Presence 3.6 and later.
To include this Node in your Task drop a "Calculate Column Aggregate" Task Element into your Task design from the Data Access Task Elements list, after which you will be presented with the following dialog box:
http://www.international-presence.com/wikidocs/images/column_aggregate_dialog.png
There are four configuration options to set for this Node:
- Data Column - Choose the Data Column that you wish to perform the calculation on.
- Aggregate Type - Choose which type of aggregate function you wish to perform
- Variable - The name variable which should store the result
- Variable Scope - see Variables for a description of variable scope.
- Rules - set the Rules for runtime evaluation.
Average
Statistical mean. This is calculated by adding together all values in the column and dividing the result by the number of records.
Example: The average of 10,15,12,11,19,7,18 is 13.142857
Median
This is the middle number if all numbers are arranged in ascending order (assuming an odd number of values). If there is an even number of values the mean is taken of the two middle values.
Example: The median of 10,15,12,11,19,7,18 is 11
Standard Deviation
Calculates the square root of the variance from the mean. This is achieved as follows:
- Calculate the mean
- For each value calculate the difference between that value and the mean
- Square that number
- Add them all together
- Divide by total number of values
- Take square root
Example: The standard deviation of 2,4,6,8,10 is 2.8243
Which equals (where m = mean, or 6.0):
- (2-m)^{2} = -4^{2} = 16
+ (4-m)^{2} = -2^{2} = 4 + (6-m>^{2} = 0^{2} = 0 + (8-m)^{2} = 2^{2} = 4 + (10-m)^{2} = 4^{2} = 16
= 40
40 / 5 = 8
Square root of 8 = 2.8243
Sum
This is the total of all the values in the data range.
Example: The sum of 10,15,12,11,19,7,18 is 92.
Minimum
This is the lowest value in the data range.
Example: The minimum of 10,15,12,11,19,7,18 is 7.
Maximum
This is the highest value in the data range.
Example: The maximum of 10,15,12,11,19,7,18 is 19.
Count
This is a count of all records.
Non-Numeric values
All non-numeric values will be ignored when calculating these results.
Data Filter | Require Columns | Append Data Column | Multiple Column Appender
Drop Column | Drop Row(s) | Calculate Column Aggregate | Dataset Splitter | Merge Data
Create Data Table | Clear Data Table | Sort Data Table | Drop Duplicate Rows | Store Data Table | Retrieve Data Table
Task Elements > Data Table Nodes > Calculate Column Aggregate