AudienceView Connect
AudienceView Documentation

Defining an Aggregate Function

The function column of the Business Intelligence|Results page, enables you to create an aggregate function for a result member. Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions can only be applied against fields whose results occur more than once.

The following table describes the available aggregate functions. The options available will depend on the result member.

Function Description
Average calculates the average value of the field across all occurrences within the record
Avg As Number for string fields, treats the values as numbers and calculates the average value of the field across all occurrences within the record
Cast As Number for string fields, treats the values as numbers
Count calculates the number of occurrences of the field (that contains a non-blank value) within the record
First Word returns the first word of a string. The first word is all characters preceding the first space or dash.
Date returns the date in the format set in Registry::Primitives::TSdateTime
Day returns the day in a numeric value (1-31)
Day of Week returns the day of the week in a numeric value (1-7) where 1=Sunday and 2=Monday.
Hour returns the hour on the date type in a numeric value (0-23) where 0=12AM and 1=1AM.
Max calculates the maximum value of the field across all occurrences within the record
Max As Number for string fields, treats the values as numbers and calculates the maximum value of the field across all occurrences within the record
Min calculates the minimum value of the field across all occurrences within the record
Min As Number for string fields, treats the values as numbers and calculates the minimum value of the field across all occurrences within the record
Month returns the month in a numeric value (1 = January and 6= June)
Sum calculates the total value of the field across all occurrences within the record
Sum As Number for string fields, treats the values as numbers and calculates the total value of the field across all occurrences within the record
Time returns the time on the date type in the format set in Registry::Primitives::TStimeFormat
To Lowercase returns the string in lowercase
To Uppercase returns the string in uppercase
Year returns the year in the format YYYY (e.g. 2011)