How is the Variance and Standard Deviation values computed in Aggregation Rows?

Document ID : KB000028514
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

How is the 'Standard Deviation' and 'Variance' computed in CA PPM when it is selected as a function on an attribute configured in a List View Aggregation Row?

 

Solution:

Aggregation rows let you view statistical data for an attribute or compare data to another attribute. When only two aggregation rows are selected (checked) to 'show', the check box to 'show' the difference between the two rows becomes active for selection.  This is referred to as the 'simple variance'.  This document will explain both the 'Simple Variance', the aggregation 'Variance' and the other functions of the Aggregation Rows.

Simple Variance:

The simple variance row shows the difference between two rows and is available when you show exactly two aggregation rows.  

The Variance is computed as : Aggregation Row 2 (r2) minus Aggregation Row 1 (r1).  This is simple math.  Therefore, negative values will be treated as expected using simple math.  

The examples below are shown with aggregation rows configured on the Project Financial Plans Tab - Cost Plan List View.

439669_0.JPG

439669_1.JPG

Example #1:

Computing the difference between the Minimum Total Cost (r2) and the Maximum Total Cost (r1) 

 

     r1: Maximum Total Cost = 9,720.00

     r2: Minimum Total Cost = 2,897.00

 

     Variance [(+r2) - (+r1)] : (+2,897.00) - (+9,720.00) = (6,823.00)

439669_2.JPG

Example #2: 

Negative value in Aggregation Row 2 (r2) will result in a negative addition result [(-r2) - (+r1)]

 

     r1: Maximum Total Cost = 9,720.00

     r2: Minimum Total Cost = (8,500.00) 

 

     Variance [(-r2) - (+r1)] : (-8,500.00) - (+9,720.00) = (18,220.00)

439669_3.JPG

Example #3:

Switch the attributes on the aggregation rows

Compute the difference between the Maximum Total Cost (r2) and the Minimum Total Cost (r1)

 

     r1: Minimum Total Cost = 2,897.00

     r2: Maximum Total Cost = 9,720.00

 

     Variance [(+r2) - (+r1)] : 9,720.00 - 2,7897.00 = 6,823.00

 

439669_4.JPG

Example #4:

Negative value in Aggregation Row 1 (r1) will result in an addition result [r2 - (-r1)]

 

     r1: Minimum Total Cost = (8,500.00) 

     r2: Maximum Total Cost = 9,720.00

 

     Variance [(+r2) - (-r1)] :  = 9,720.00 - (-8,500.00) = 18,220.00   

 

Aggregation Row Functions:

When creating aggregation rows, the available statistical functions that can be configured for numeric attributes are: Sum, Count, Average, Minimum, Maximum, Standard Deviation and Variance (not the same as 'simple variance').  

Note: If you manually compute the 'Variance' using the values you see in the application, your amount may not match the aggregation row.  The internal computations are using the natural results of the raw data (some data may have fractional values with more than 2 significant decimal values). No rounding is done on the computational data.  The final amount shown is displayed with rounding to the last digit.     

  • Maximum - Matches the amount for one row that has the highest numeric value   
  • Minimum - Matches the amount for one row that has the lowest numeric value   
  • Count - Number of rows in results with a non-null value (null cells are not included in the count)   
  • Sum - Total amount of all non-null values   
  • Average - Also known as the Mean. Sum divided by Count  
  • Variance - The average of the squared differences of the Mean (not the same as 'simple variance')  
1. Calculate the mean (M) which is the same as the 'Average' 
2. For each number (n) in the series, subtract the mean (n-M) and square the result (n-M)²    
3. Add these squared results together to get a total sum  ∑[(n-M)² + (n-M)² + (n-M)²...]  
4. Divide the total by the count to get the Variance   ∑[(n-M)² + (n-M)² + (n-M)²...] / Count 
  • Standard Deviation - Square Root of the Variance   

 

The example configuration is shown with aggregation rows configured on the Project Financial Plans Tab - Cost Plan List View

Note: The 'simple' Variance checkbox field is not available for 'Show' because more than two aggregation rows are defined.  

439669_6.JPG          439669_7.JPG