Aggregation Functions

These functions are available for Crosstabs only. They produce an aggregated value. This is then often used in conjunction with another aggregated value to produce the final output in a Crosstab.

Function Average( <Expression> )
Description Returns the average of all values in the specified expression (field). This only returns the average of fields that do not contain NULLs.

Note: This option is only available when using the RowTotal total type.
Arguments The expression should be a numeric field.
Examples
  • Average(Total_Value)
Function AverageAll( <Expression> )
Description Returns the average of all values in the specified expression (field). This returns the average of ALL fields including those that contain NULLs.

Note: This option is only available when using the RowTotal total type.
Arguments The expression should be a numeric field.
Examples
  • AverageAll(Total_Value)
Function PercentileSum(<Expression> ,<Expression> ,<Expression> )
Description Returns the sum of n% of a specified expression (field).
Arguments

The 1st expression should be a numeric field.

The 2nd expression should be numeric and is the start point, in %, of the range to be used.

The 3rd expression should be numeric and is the end point, in %, of the range to be used.
Examples
  • To return the sum of the top 10% of values: PercentileSum(Sale_Price, 90, 100)
  • To return the sum of the bottom 25% of values: PercentileSum(Sale_Price, 0, 25)
Function PercentileValue(<Expression> ,<Expression> )
Description

Returns the percentile value of the specified expression (field). This is the value below which, a given percentage of observations in a group of observations falls. E.g. the 20th percentile is the value below which 20% of the observations may be found.

Arguments

The 1st expression should be a numeric field.

The 2nd expression should be a numeric value that specifies the percentile value to be used by the function.
Examples
  • To return the limit of the lower quartile (bottom 25%) of values: PercentileValue(Sale_Price, 25)
  • To return the limit of the median (bottom 50%) of values: PercentileValue(Sale_Price, 50)
Function PercentileAverage(<Expression> ,<Expression> ,<Expression>)
Description Returns the average of the values that are between a percentile range given for the specified expression (field).
Arguments

The 1st expression should be a numeric field.

The 2nd expression should be a numeric value that specifies the lower percentile value to be used by the function and the 3rd expression should be a numeric value that specifies the upper percentile value to be used.
Examples
  • To return the average of the lower quartile (bottom 25%) of values: PercentileAverage(Sale_Price, 0, 25)
  • To return the average of the values between the 25th percentile and the 50th percentile: PercentileAverage(Sale_Price, 25, 50)
Function PercentileSum (<Expression> ,<Expression> ,<Expression>)
Description Returns the number of values that are between a percentile range given for the specified expression (field).
Arguments

The 1st expression should be a numeric field.

The 2nd expression should be a numeric value that specifies the lower percentile value to be used by the function and the 3rd expression should be a numeric value that specifies the upper percentile value to be used.
Examples
  • To return the number of values in the lower quartile (bottom 25%) of values: PercentileCount(Sale_Price, 0, 25)
  • To return the number of values between the 25th percentile and the 50th percentile: PercentileCount(Sale_Price, 25, 50)
Function PercentileMin (<Expression> ,<Expression> ,<Expression>)
Description Returns the Minimum data value within a percentile range given for the specified expression (field).
Arguments

The 1st expression should be a numeric field.

The 2nd expression should be a numeric value that specifies the lower percentile value to be used by the function and the 3rd expression should be a numeric value that specifies the upper percentile value to be used.
Examples
  • To return the Minimum data value of the lower quartile (bottom 25%) of values: PercentileMin(Sale_Price, 0, 25)
  • To return the Minimum data value between the 25th percentile and the 50th percentile: PercentileMin(Sale_Price, 25, 50)
Function PercentileMax (<Expression> ,<Expression> ,<Expression>)
Description Returns the Maximum data value within a percentile range given for the specified expression (field).
Arguments

The 1st expression should be a numeric field.

The 2nd expression should be a numeric value that specifies the lower percentile value to be used by the function and the 3rd expression should be a numeric value that specifies the upper percentile value to be used.
Examples
  • To return the Maximum data value of the lower quartile (bottom 25%) of values: PercentileMax(Sale_Price, 0, 25)
  • To return the Maximum data value between the 25th percentile and the 50th percentile: PercentileMax(Sale_Price, 25, 50)
Function Sum( <Expression> )
Description Returns the sum of the values in the specified expression (field).

Note: This option is only available when using the RowTotal total type.
Arguments The expression should be a numeric field.
Examples
  • Sum(Total_Value)
Function CountDistinct (<Expression> , <’String Expression’>)
Description Returns the distinct count that the dynamic view would return.
Arguments The expression should be a numeric field and the string expression is the name of the dynamic view.
Examples
  • CountDistinct (1, ‘dynamic view name’)
Function In (<Expression> , <Expression>)
Description Determines whether a specified value appears in a field
Arguments The first expression is the field of interest and the second expression is the value(s) searched for.
Examples

CASE WHEN In( Day_of_Week , 2, 3, 4) THEN 1 ELSE 0 END

  • Would count records where Day_of_Week = 2,3 or 4.
Function IsColumnTotal
Description References a column total or nested column total.
Arguments  
Examples  
Function

IsGrandTotal

Description References a grand total
Arguments  
Examples  
Function IsRowTotal
Description References a row total or nested row total.
Arguments  
Examples  
Function IsSubTotal
Description References a Subtotal
Arguments  
Examples