MDX Queries and Syntax

Using Derived Statistics with the Aggregate Function

When the aggregate function is used in a calculated member, the statistic associated with the current measure will determine how the values are aggregated. For example:

WITH                                                                            
  MEMBER [measures].[calc] AS '                                                 
    [measures].[actual_max]-[measures].[actual_min]'                            

  MEMBER [time].[agg complexfunc] AS                                           
    'aggregate([time].[all time].[1994].children)'                              

SELECT                                                                          
  {[time].[all time].[1994].children, [time].[agg complexfunc]} ON 0,           
  {measures].[actual_max], [measures].[actual_min],                            
   [measures].[actual_sum], [masures].[actual_n],                              
   [measures].[actual_avg], measures.calc} on 1                                 
FROM [prdmddb]

This example returns the following:

                      1            2            3            4 agg complexfunc  
actual_max    $1,000.00      $987.00      $992.00    $1,000.00       $1,000.00  
actual_min       $13.00        $3.00       $20.00       $15.00           $3.00  
actual_sum   $89,763.00   $93,359.00   $89,049.00   $88,689.00     $360,860.00  
actual_n          180          180          180          180             720  
actual_avg      $498.68      $518.66      $494.72      $492.72         $501.19  
     calc          987          984          972          985             997

For each current measure listed on the left, the aggregate function does the following:

actual_max	It takes the MAX of the actual_max values associated with the children of 1994.
actual_min	It takes the MIN of the actual_min values associated with the children of 1994.
actual_sum	It summarizes the actual_sum values associated with the children of 1994.
actual_n	It counts the actual_n values associated with the children of 1994.
actual_avg	It divides the aggregated sum of the children of 1994 by the aggregated count value of the children of 1994.
calculated measure calc	It takes the MAX of the actual_max values for the children of 1994 (1000) and subtracts the MIN of the actual_min values for the children of 1994 (3).

Example 2

This example shows what happens when the query is changed to include a numeric expression:

WITH                                                                            
  MEMBER [time].[agg complexfunc] AS                                            
    'aggregate([time].[all time].[1994].children, measures.actual_max + 1)'     

SELECT                                                                          
  {[time].[all time].[1994].children, [time].[agg complexfunc]} ON 0,           
  {[measures].[actual_max], [measures].[actual_min],                            
   [measures].[actual_sum], [measures].[actual_n],                              
   [measures].[actual_avg]} on 1                                                
FROM [prdmddb]

This example returns the following:

                                                                    

                      1            2            3            4 agg complexfunc  
actual_max    $1,000.00      $987.00      $992.00    $1,000.00       $1,001.00  
actual_min       $13.00        $3.00       $20.00       $15.00         $988.00  
actual_sum   $89,763.00   $93,359.00   $89,049.00   $88,689.00       $3,983.00  
actual_n          180          180          180          180            3983  
actual_avg      $498.68      $518.66      $494.72      $492.72

For each current measure listed on the left, the aggregate function does the following::

actual_max	It takes the MAX of the actual_max values associated with the children of 1994 and adds 1 to it.
actual_min	It takes the MIN of the actual_max values associated with the children of 1994 and adds 1 to it.
actual_sum	It adds 1 to each of the actual_max values associated with the children of 1994 and SUMs the values.
actual_n	It adds 1 to each of the actual_max values associated with the children of 1994 and SUMs the values.
actual_avg	It cannot compute a derived measure based on another measure, so it returns a missing value.

Example 3

This example shows what happens when the query is changed to include a numeric expression with measures that aggregate differently.

WITH                                                                            
  MEMBER [time].[agg complexfunc] AS                                            
    'aggregate([time].[all time].[1994].children, measures.actual_max -         
measures.actual_min)'                                                           

SELECT                                                                          
  {[time].[all time].[1994].children, [time].[agg complexfunc]} ON 0,           
  {[measures].[actual_max], [measures].[actual_min],                            
   [measures].[actual_sum], [measures].[actual_n],                              
   [measures].[actual_avg]} on 1                                                
FROM [prdmddb]

This example returns the following:

                      1            2            3            4 agg complexfunc  
actual_max    $1,000.00      $987.00      $992.00    $1,000.00         $987.00  
actual_min       $13.00        $3.00       $20.00       $15.00         $972.00  
actual_sum   $89,763.00   $93,359.00   $89,049.00   $88,689.00       $3,928.00  
actual_n          180          180          180          180            3928  
actual_avg      $498.68      $518.66      $494.72      $492.72

For each current measure listed on the left, the aggregate function does the following:

actual_max	It subtracts the actual_min value associated with each child of 1994 from the corresponding actual_max value. It picks the MAX of these values (1000-13).
actual_min	It subtracts the actual_min value associated with each child of 1994 from the corresponding actual_max value. It picks the MIN of these values (992 - 20).
actual_sum	It subtracts the actual_min value associated with each child of 1994 from the corresponding actual_max value. It then sums all of these values.
actual_n	It subtracts the actual_min value associated with each child of 1994 from the corresponding actual_max value. It then sums all of these values.
actual_avg	It cannot compute a derived measure based on other measures, so it returns a missing value.

Example 4

This example shows what happens when the query is changed to have the aggregate function on a calculated measure, and the numeric expression is the actual_avg measure.

WITH                                                                            
  MEMBER [measures].[agg complexfunc] AS                                        
    'aggregate([time].[all time].[1994].children, measures.actual_avg)'         

SELECT                                                                          
  {[measures].[actual_sum], [measures].[actual_n],                              
    [measures].[agg complexfunc]}ON 0,                                         
  {[time].[all time].[1994].children} ON 1                                      
FROM [prdmddb]

This example returns the following:

           actual_sum  actual_n  agg complexfunc                                
       1   $89,763.00       180 501.194444444444                                
       2   $93,359.00       180 501.194444444444                                
       3   $89,049.00       180 501.194444444444                                
       4   $88,689.00       180 501.194444444444

The current measure is the calculated measure [agg complexFunc]. However, using this would cause infinite recursion, so the aggregate function aggregates based only on the numeric expression. In this case, the statistic is average, which divides the sum by the count. For each child of 1994, the sum is divided by the count, and these values are summed together. This total is then divided by the number of children of 1994 to give the aggregate value.

Example 5

This example shows what happens when the numeric expression is changed to an expression that used a derived statistic.

WITH                                                                            
  MEMBER [measures].agg complexfunc] AS                                        
    'aggregate([time].[all time].[1994].children, measures.actual_avg + 12)'    

SELECT                                                                          
  {[measures].[actual_sum], [measures].[actual_n],                              
    [measures].[agg complexfunc]} ON 0,                                         
  {[time].[all time].[1994].children} ON 1                                      
FROM [prdmddb]

This example returns the following:

           actual_sum     actual_n agg complexfunc                              
       1   $89,763.00          180                                              
       2   $93,359.00          180                                              
       3   $89,049.00          180                                              
       4   $88,689.00          180

In this case, the value of the aggregation is missing. When measures that are associated with derived statistics are used in an expression for the aggregate function, it is not able to calculate the correct value, so it simply returns missing.

Example 6

This example shows what happens when the query is changed to have a standard statistic in the expression.

WITH                                                                            
  MEMBER [measures].[agg complexfunc] AS                                        
    'aggregate([time].[all time].[1994].children, measures.actual_max + 12)'    

SELECT                                                                          
  {[measures].[actual_max],                                                     
    [measures].[agg complexfunc]} ON 0,                                         
  {[time].[all time].[1994].children} ON 1                                      
FROM [prdmddb]

This example returns the following:

           actual_max agg complexfunc                                           
       1    $1,000.00            1012                                           
       2      $987.00            1012                                           
       3      $992.00            1012                                           
       4    $1,000.00            1012

In this case, the aggregate function looks for the max value associated with the actual_max measure for the children of 1994. Then 12 is added to this value.

Example 7

This example shows what happens when the query is changed to still have the aggregate function on a calculated measure, and it has a numeric expression that includes measures that aggregate differently.

WITH                                                                            
  MEMBER [measures].[agg complexfunc] AS                                        
    'aggregate([time].[all time].[1994].children, measures.actual_max +         
measures.actual_min)'                                                           

SELECT                                                                          
  {[measures].[actual_max], measures.actual_min,                                
    [measures].[agg complexfunc]} ON 0,                                         
  {[time].[all time].[1994].children} ON 1                                      
FROM [prdmddb]

This example returns the following:

                                                                                                                             
           actual_max   actual_min agg complexfunc                              
       1    $1,000.00       $13.00                                              
       2      $987.00        $3.00                                              
       3      $992.00       $20.00                                              
       4    $1,000.00       $15.00

In this case, one measure is a max and the other a min. It is unclear how to aggregate the values, so a missing value is returned.

Standard Statistics

Here are the standard statistics and how they are aggregated.

Standard Statistic	How it is aggregated
MAX	Get the MAXIMUM of the values
MIN	Get the MINIMUM of the values
N	Count the values
NMISS	Sum the NMISS values
SUM	Sum the SUM values
SUMWGT	Sum the SUMWGT values
USS	Sum the USS values
UWSUM	Sum the UWSUM values

Derived Statistics

For measures associated with these statistics, the system will use the values that are being aggregated to determine the result value based on the statistic. For example, for AVG, it will take the SUM of the values and divide it by the N of the values. Here are the derived statistics:

AVG
RANGE
CSS
VAR
STD
ERR
CV
T
PRT
LCLM
UCLM
NUNIQUE

Top of Page