Supported Aggregation Functions

Provides an overview of supported aggregation functions.

Use aggregate functions in custom metrics. Custom metrics can also include the use of row-level functions, conditional CASE expressions, statistical functions, an expansive list of arithmetic functions, as well as FIRST and LAST values. In addition, they can be filtered (using date and time filter functions and SQL-like expressions).

Data can be aggregated using column, table, or window aggregation functions. Each is explained in the links below. Aggregation functionality is explained in the links below.

• Conditional CASE Expressions
• Supported Statistical Functions
• Arithmetic Functions
• Column Aggregation Functions
• Table Aggregation Functions
• Window Aggregation Functions
• Date and Time Filter Aggregation Functions
• Metric Aggregation Functions

Column Aggregation Functions

Column aggregation functions aggregate data using all the data displayed on a visual. They group results in the same way that the visual itself groups its data. For example, if your visual shows data grouped by gender (male and female), then column aggregation functions return two results, one for males and one for females. Only data included in the visual by any filters that have been applied are included in the results.

The following table describes the supported column aggregation functions.

Function	Parameter Type	Description
AVG(<field>)	numeric	Returns the average of a column (field), grouped in the same manner as the visual data.
COUNT(<field>)	any	Returns the numeric count of values in a column (field), grouped in the same manner as the visual data. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
COUNTD(<field>)	any	Returns the numeric count of unique values in a column (field), grouped in the same manner as the visual data. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
MAX(<field>)	numeric	Returns the maximum value of a column (field), grouped in the same manner as the visual data.
MIN(<field>)	numeric	Returns the minimum value of a column (field), grouped in the same manner as the visual data.
SUM(<field>)	numeric	Returns the sum of a column (field), grouped in the same manner as the visual data.
FIRST_VALUE(<field>)	any	Returns the first value of a given expression in the group, as when the expression is sorted in the ascending order.
LAST_VALUE(<field>)	any	Returns the last value of a given expression in the group, as when the expression is sorted in the ascending order.
STDDEV_POP(<field>)	numeric	Computes the population standard deviation and returns the square root of the population variance.
STDDEV_SAMP(<field>)	numeric	Computes the cumulative sample standard deviation and returns the square root of the sample variance.
VAR_POP(<field>)	numeric	Returns the population standard variance of a given expression.
VAR_SAMP(<field>)	numeric	Returns the sample variance of a given expression.
MEDIAN(<field>)	numeric	Computes the median value across the group.

Example

Suppose you have the following fields and data in a data source:

name	gender	city	earned	spent
Alan	M	Rockville	$10	$2
Bob	M	Rockville	$8	$3
Carol	F	Rockville	$5	$5
Darlene	F	Reston	$4	$6
Ed	M	Reston	$2	$8

To use this data set to create a custom metric called Leftover (a group’s leftover money), use the following formula.

SUM( earned ) - SUM( spent )

If you used the Leftover custom metric in a visual grouping by gender using the data above, you would get the results shown below.

Gender	Leftover
F	-2
M	7
Total	5

Males have $7, derived from (10+8+2) - (2+3+8). Females have -$2 left over, derived from (5+4) - (5+6). If you used the same custom metric in a visual grouping by city, you would see Rockville having $13, from (10+8+5) - (2+3+5), and Reston having -$8, from (4+2) - (6+8).

Date and Time Filter Aggregation Functions

To filter a custom metric using dates or times, you must already have a time attribute configured in your data source. The following date and time functions can only be used after WHERE in your custom metric.

Date field options use common time formats such as YTD, MMDDYYYY, and YoY.

The following date and time filter aggregation functions are supported.

Supported Date and Time Functions
Function	Description
DATE()	Deprecated. Use NOW() instead.
DateADD('<time_period>',<interval>,'<date>')	Deprecated. Use TIME_ADD instead. For example, consider this DateADD specification: DateADD('YEAR', 1, '2021-01-01') Use this TIME_ADD specification instead: TIME_ADD('YEAR', 1, '2021-01-01') In a second example, consider this DateADD specification: DateADD('MONTH', 1, DATE()) Use this TIME_ADD specification instead: TIME_ADD('YEAR', 1, NOW())
DateSUB('<time_period>',<interval>,'<date>')	Deprecated. Use TIME_ADD instead, specifying a negative number for interval. For example, consider this DateADD specification: DateSUB('YEAR', 1, '2021-01-01') Use this TIME_ADD specification instead: TIME_ADD('YEAR', -1, '2021-01-01' TIME_ADD supports negative interval numbers for subtraction.
NOW()	Obtains the current date and time for the derived field. NOW() functionality is available when you use a supported connector. Set the calculations.rle.now.function property in the query-engine.properties file to true and restart the query engine microservice. See Query Engine Properties.
PreviousPeriod(<offset>,<numPeriods>)	This function is supported only within a TRANSFORM clause used for filtering the custom metric. The period returned is of the same length as the currently represented period, but not immediately prior to it. Instead, it counts back in <numPeriods> periods of time, measured in units named by <offset>. The following time <offset> values are supported: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND. See PreviousPeriod Function.
TIME()	Deprecated. Use NOW() instead.
TIME_ADD('<time-period>',<interval>, <date-time-field>)	Adds an interval value to the <timepart> of the date-time field: In the following example, 7 is added to the hour in the field called date_time_field: TIME_ADD ('HOUR', +7, date_time_field)

Date Filter Functions

Specific parameters are needed for the DateADD and DateSub functions. The following table describes them.

Parameter	Value
time_period	Supported time periods (with corresponding interval range): YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND
interval	Whole number integer. Negative numbers are supported for subtraction.
date	Current day operator: date() Standard date and time formats supported include: yyyy-MM-dd HH:mm:ss MM/dd/yy hh:mm aa yyyy For all supported formats, see Convert Attributes to Time Fields in Data Source Field Specifications.

PreviousPeriod Function

The PreviousPeriod function is used for comparing data values between different time periods. This function can be used when you need to compare one time period to another of equivalent size for variance custom metrics. For example, comparing results from the current month to the previous month or the current week to the same week one year ago.

Note that this function only works when the date field used in the formula is selected on the time bar.

To use this function, the TRANSFORM SQL-like expression must be used in the custom metric to convert the date range for a specified time attribute. For example:

SUM(Sales) TRANSFORM saledate = PreviousPeriod('month',1)

If the saledate time period is March 2015, the custom metric returns SUM(Sales) where the saledate is February 2015.

If the data is grouped by the same field for which a PreviousPeriod transformation is performed and it is grouped by days but transformed by units of months, quarters, or years, null values are returned when the previous period does not have matching days for the current period. For example, if the current period is the month of March and PreviousPeriod('month',1) is used for the transformation, null values are produced for February 29-31, 2015 because those days are not valid days (although they are valid days for March 2015). Self-Service Analytics attempts to preserve the day-of-month correspondence between the two periods.

Specific parameters must be specified in PreviousPeriod functions. The following table describes them.

Parameter	Value
offset	The time granularity for the previous period (includes YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND).
numPeriods	The argument specifying the number of periods to go back in time.

Table Aggregation Functions

Table aggregation functions are broader in scope than column aggregation functions. Table aggregation functions use all data from a field and produce a single, ungrouped value. You typically do not use the result directly in a visual, since it is ungrouped.

For example, if you have sales records grouped by gender, a TableSUM custom metric returns the total sales of all records as one value, regardless of the group in consideration. The TableSUM result would include both the male and female data values. Consequently, males and females would appear to have the same sales if the TableSUM result was included in the visual.

Table aggregation functions are typically used to calculate percentages of a whole or average values.

The following table describes the supported table aggregation functions.

Function	Type	Description
TableAVG(<field>)	numeric	Returns the average of a column (field), regardless of how the visual is grouped.
TableCOUNT(<field>)	any	Returns the numeric count of values in a column (field), regardless of how the visual is grouped. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
TableCOUNTD(<field>)	any	Returns the numeric count of unique values in a column (field), regardless of how the visual is grouped. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
TableMAX(<field>)	numeric	Returns the maximum value of a column (field), regardless of how the visual is grouped.
TableMIN(<field>)	numeric	TableMIN returns the minimum value of a column (field), regardless of how the visual is grouped.
TableSUM(<field>)	numeric	TableSUM returns the sum of a column (field), regardless of how the visual is grouped.

Example

Suppose you have the following fields and data:

name	gender	city	earned	spent
Alan	M	Rockville	$10	$2
Bob	M	Rockville	$8	$3
Carol	F	Rockville	$5	$5
Darlene	F	Reston	$4	$6
Ed	M	Reston	$2	$8

Using this data, you can create a custom metric containing individual earnings as a percentage of total earnings with the following formula:

SUM(earned) / TableSUM(earned) * 100

in which:

• SUM(earned) calculates the sum earnings for each individual.
• TableSUM(earned) calculates the total earnings of all records in the data.
• The quotient of SUM(earned) / TableSUM(earned) is multiplied by 100 to convert the result into a percentage.

Shown on a table using the example data above, the results would look like this:

Name	% of Whole
Alan	34.48
Bob	27.59
Carol	17.24
Darlene	13.79
Ed	6.90
Total	100

Window Aggregation Functions

Window aggregation functions are a middle case between column and table aggregation functions. They provide a snapshot or window into a subset of data, depending on the groupings used by the visual. Each window function such as WindowSUM or WindowAVG requires a numeric field to aggregate followed by a list of one or more attributes. The function aggregates the data and groups the results based on these attributes if the attributes are present in the visual. Attributes absent from the visual are ignored from the aggregation.

Derived fields can be used in window aggregation functions.

For example, an aggregation WindowAVG( profits, gender, city ) returns the average profits in the data, grouped by gender and city if gender and city are represented in the visual. If gender happens to be absent from the visual, then it is dropped from the aggregation. Effectively, the average profits would then be grouped only by city.

The following table describes the supported window aggregation functions.

Function	Type	Description
WindowAVG(<field>,<attr1>[,<attr2>]...)	numeric	Returns the average of a column (field), grouped by the specified attributes.
WindowCOUNT(<field>,<attr1>[,<attr2>]... )	any	Returns the numeric count of values in a column (field), grouped by the specified attributes. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
WindowCOUNTD(<field>,<attr1>[,<attr2>]...)	any	Returns the numeric count of unique values in a column (field), grouped by the specified attributes. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for <field> to include null values for the field in the count.
WindowMAX(<field>,<attr1>[,<attr2>]...)	numeric	Returns the maximum value of a column (field), grouped by the specified attributes.
WindowMIN(<field>,<attr1>[,<attr2>]...)	numeric	Returns the minimum value of a column (field), grouped by the specified attributes.
WindowSUM(<field>,<attr1>[,<attr2>]...)	numeric	Returns the sum of a column (field), grouped by the specified attributes.

Example

Suppose you have the following fields and data:

name	gender	city	earned	spent
Alan	M	Rockville	$10	$2
Bob	M	Rockville	$8	$3
Carol	F	Rockville	$5	$5
Darlene	F	Reston	$4	$6
Ed	M	Reston	$2	$8

To create a custom metric containing a group’s contribution to just gender, rather than to the whole, use the following formula.

SUM(earned)/WindowSUM(earned,gender) * 100

Using this custom metric in a pivot table with the example data set shown above produces results similar to the ones shown below.

City	Gender	Volume	% of Each Gender’s Earnings
Reston	F	1	44.44
	M	1	10
Rockville	F	1	55.56
	M	2	90
Total		5	100

In this pivot table, each city’s total earnings (SUM(earned)) is shown as a percentage of each gender’s total earnings. If gender had been absent from the visual, the cities’ total earnings would have been shown as totals of the whole, rather than of each gender.

Arithmetic Functions

Custom metrics support the following arithmetic functions in your aggregate functions.

The following table describes the supported arithmetic functions.

Function	Type	Description
POWER(numeric1, numeric2)	numeric	Returns numeric1 raised to the power of numeric2.
MOD(numeric1, numeric2)	numeric	Returns the remainder (modulus) of numeric1 divided by numeric2. The result is negative only if numeric1 is negative.
SQRT(numeric)	numeric	Returns the square root of numeric.
LN(numeric)	numeric	Returns the natural logarithm (base e) of numeric.
LOG10(numeric)	numeric	Returns the base 10 logarithm of numeric.
EXP(numeric)	numeric	Returns e raised to the power of numeric.
CEIL(numeric)	numeric	Rounds numeric up, returning the smallest integer that is greater than or equal to numeric.
FLOOR(numeric)	numeric	Rounds numeric down, returning the largest integer that is less than or equal to numeric.
ROUND(numeric1, numeric2)	numeric	Rounds numeric1 to optionally numeric2 (if not specified 0) places right to the decimal point.
RAND(seed)	numeric	Generates a random double between 0 and 1 inclusive, optionally initializing the random number generator with seed.
SIGN(numeric)	numeric	Returns the signum of numeric.
SIN(numeric)	numeric	Returns the sine of numeric.
COS(numeric)	numeric	Returns the cosine of numeric.
TAN(numeric)	numeric	Returns the tangent of numeric.
COT(numeric)	numeric	Returns the cotangent of numeric.
ASIN(numeric)	numeric	Returns the arcsine of numeric.
ACOS(numeric)	numeric	Returns the arc cosine of numeric.
ATAN(numeric)	numeric	Returns the arctangent of numeric.
PI()	numeric	Returns a value that is closer than any other value to pi.
DEGREES(numeric)	numeric	Converts numeric from radians to degrees.
RADIANS(numeric) >	numeric	Converts numeric from degrees to radians.

Conditional CASE Expressions

You can include the use of CASE expressions (singular or nested) in your custom metrics, much as you would row-level case and SQL case expressions. These capabilities include:

Conditions in when :

• Condition must be an aggregate-level expression.
• Can compare both metrics and groups. Group values, or any other non-numeric values can be used through FIRST_VALUE / LAST_VALUE functions.
• Use existing metrics from the request or add new metrics.
• Row-level expressions can be used inside aggregation functions.
• Use AND / OR operators to build complex conditions.
• where and transform clauses can be used in conditions to modify aggregate expressions.

To return results in thenthat include custom metric expressions, including calculated sub-queries.

• Must be an aggregate-level expression.
• All result values must be of the same type.
• Can be a numeric or non-numeric value.
• where and transform clauses can be used in conditions to modify aggregate expressions.

Additionally, you can nest case functions if needed, both for conditions and results. If you use case expressions as part of an arithmetic expression, it must be enclosed in parentheses.

Only one result branch is returned from the expression, but all branches will be evaluated simultaneously, regardless of which condition is met first.

Supported Statistical Functions

Type	Parameter Type	Description
STDDEV_POP	numeric	Computes the population standard deviation and returns the square root of the population variance.
STDDEV_SAMP	numeric	Computes the cumulative sample standard deviation and returns the square root of the sample variance.
VAR_POP	numeric	Returns the population standard variance of a given expression.
VAR_SAMP	numeric	Returns the sample variance of a given expression.
MEDIAN	numeric	Computes the median value across the group.

Supported Row-Level Functions

Use row-level functions in the row-level expressions you use to create the following calculation types:

• derived fields
• custom metrics
• admin-defined functions

The row-level functions described in this section are fully supported by all three types. For information about the aggregate functions and SQL-like expressions you can use in custom metrics, see Supported Aggregation Functions and Supported SQL-Like Expressions.

Row-level functions are divided into the following categories:

• Arithmetic Functions
• Conditional Functions
• Logical Functions
• Numerical Functions
• Relational Functions
• Text Functions
• Time Functions

Arithmetic Functions

Operator	Description
- (SUBTRACT)	Subtracts one numeric value from another
* (MULTIPLY)	Multiply one number by another
/ (DIVIDE)	Divide one number by another
+ (ADD)	Addition: adds two numbers

Conditional Functions

Operator	Description
CASE	Use the CASE function is the same manner as standard SQL CASE functions. CASE can be used to list a series of conditions and return an appropriate value for the first condition that is met.
COALESCE	Use the COALESCE function in the same manner as standard SQL COALESCE functions. COALESCE can be used to return the first non-null value in a list of values. The COALESCE function also supports aggregate metrics with complex calculations using arithmetic functions (for example COALESCE(max(sales) * 1.3, 0)), so a default value can be used if null values are returned.

Logical Functions

Operator	Description
AND	Evaluates to TRUE if both boolean expressions are TRUE.
BETWEEN	Evaluates to TRUE if the operand is within a range.
IN	Evaluates to TRUE if the operand is equal to one of a list of expressions.
NOT	Reverses the value of any other Boolean operator.
OR	Evaluates to TRUE if either boolean expression is TRUE.

Numerical Functions

Function	Description	Example
CEIL	Returns the smallest integer value that is not less than the passed Value	CEIL(value : Numeric) : Numeric CEIL(Field_A)
FLOOR	Returns the largest integer value that is not greater than the passed value	FLOOR(value : Numeric) : Numeric FLOOR(Field_A)
NUM_TO_TEXT	Converts the numeric expression to text	NUM_TO_TEXT(value : Numeric) NUM_TO_TEXT(Field_A)
ROUND	Rounds a numeric value to the number of decimals specified	ROUND(Field_A, 0)
UNIX_TIME_TO_TIME	Converts the numeric expression to time	UNIX_TIME_TO_TIME(Field_Milliseconds /1000)

Relational Functions

Operator	Description
!=	Checks whether the values of two operands are not equal. If the values are not equal, then the condition is true.
<	Checks whether the value of the left operand is less than the value of the right operand. If it is, then the condition is true.
<=	Checks whether the value of the left operand is less than or equal to the value of the right operand. If it is, then the condition is true.
=	Checks whether the values of two operands are equal. If the values are equal, then the condition is true.
>	Checks whether the value of the left operand is greater than the value of the right operand. If it is, then the condition is true.
>=	Checks whether the value of the left operand is greater than or equal to the value of right operand. If it is, then condition is true.

You can also combine less than (<) and greater than (>) functions using logical AND processing in the same statement. For example, the following are valid statements:

saledate > 2020-10-28 AND saledate < 2020-10-30
saledate > 2020-10-28 AND saledate < 2020-10-30 AND state = 'CA'

In each of these examples, the individual relational functions must all be true for the full statement to be true. In the second example, the sale date must be greater than October 28, 2020 and less than October 30, 2020 and the sale must take place in the state of California.

Text Functions

Function	Description	Example
CONCAT	Returns a text that is the result of concatenating two or more text values.	CONCAT(Field_FirstName, ' ,', Field_LastName)
LENGTH	Returns the number of characters of the specified string.	LENGTH(SUBSTRING('$12456.00', 2, 10))
LOCATE	Finds the first occurrence of substring in a string, starting at position.	LOCATE('Mr.', CONCAT(Field_FirstName, ' ,', Field_LastName), 0)
LOWER	Returns the argument in lowercase.	LOWER(SUBSTRING(Field_A, 0, 3 ))
LPAD	Returns the text argument, left-padded with the text specified by padString to a length of Length characters.	LPAD(SUBSTRING(Field_A, 0, 15), 3, 'abc')
LTRIM	Returns a text value after removing leading blanks.	LTRIM(SUBSTRING(Field_A, 0, 5))
RPAD	Returns the Text argument, right-padded with the text specified by padString to a length of Length characters.	RPAD(SUBSTRING(Field_A, 0, 15), 3, 'abc')
RTRIM	Returns a text value after removing trailing blanks.	RTRIM(SUBSTRING(Field_A, 0, 5))
SUBSTRING	Returns the substring of String value which begins at position defined by Start and is Length characters long.	SUBSTRING(Field_A, 4, 3)
TEXT_TO_NUM	Converts the text string to numeric.	TEXT_TO_NUM(LTRIM(Field_A))
TEXT_TO_TIME	Converts the text expression to time according to the specified format. This function requires input in the form of an attribute or string field containing data that could be parsed as a time field and the format for the time field. Valid formats must be enclosed in single quotation marks and can only use the following syntax elements: YYYY (for years), MM (for months), DD (for days), HH24 (for hours), MI (for minutes), SS (for seconds), and MS (for milliseconds). Separators in the syntax that are allowed are - (dashes), : (colons), . (periods), / (backslashes), and spaces.	TEXT_TO_TIME(Field_A,'YYYY-MM-DD HH24:MI:SS')
UPPER	Returns the argument in uppercase.	UPPER(SUBSTRING(Field_A, 0, 3))

Time Functions

The following time functions are supported. Valid values for <timepart> vary, based on the Self-Service Analytics connector selected, but can include YEAR, QUARTER, MONTH, WEEK, WEEK_OF_YEAR, WEEK_OF_MONTH, DAY, DAY_OF_YEAR, DAY_OF_MONTH, DAY_OF_WEEK, HOUR, MINUTE, SECOND, or MILLISECOND. Review the documentation for the Self-Service Analytics connector for any deviations from this list. Note that the WEEK_OF_YEAR function calculates the week from January 1, not from the week containing January 1.

Function	Description
EXTRACT	Extracts the <timepart> of the <datetime> field: extract(<timepart>,<datetime>)
NOW	Obtains the current date and time for the derived field. NOW() functionality is available when you use a supported connector. Set the calculations.rle.now.function property in the query-engine.properties file to true and restart the query engine microservice. See Query Engine Properties. CASE WHEN [date column] = '' NOW() ELSE [date column] END
TIME_ADD	Adds an interval value to the <timepart> of the <datetime> field: time_add(<timepart>, <interval>, <datetime>) In the following example, 7 is added to the hour in the field called date_time_field: TIME_ADD (hour, +7, date_time_field)
TIME_DIFF	Returns the time difference between two time fields in the unit you request: time_diff('<timepart>', <end_date_field>, <start_date_field>) In the following example, the difference between the values of the ENDDATE and STARTDATE fields is returned in days: time_diff('DAY', ENDDATE, STARTDATE)
TIME_TO_UNIX_TIME	Returns the value of a <datetime> field as a Unix time stamp: time_to_unix_time(<datetime>)
TRUNCATE_TIME	Rounds (Truncates) the <datetime> field value down to the granularity specified by <timepart>: truncate_time(<timepart>,<datetime>)

Metric Aggregation Functions

provides a set of metric functions that are used to group (aggregate) data. The following aggregation methods can be selected for metrics in your visuals. See also Metrics.

Aggregation Function	What Is Returned
AVG	The average of the data values for the field. This function is available only for numeric fields.
DISTINCT COUNT	The total number of unique values for the field. This function is available only for attribute and numeric fields.
COUNT	The total number of values for the field. This function is available only for attribute and numeric fields.
MIN	The lowest value in all the data values for the field. This function is available only for numeric fields.
MAX	The highest value in all the data values for the field. This function is available only for numeric fields.
SUM	The total of all the data values for the field. This function is available only for numeric fields.
LAST VALUE	The last value in all the data values for the field, sorted by the time attribute selected for the time bar. If the latest date and time for the time attribute is exactly the same in multiple records, the last value for the field is the maximum value of the field in the records with the latest date and time. See LAST VALUE Examples. This function is available only for numeric fields.
NO AGGREGATION	No Aggregation is available if you group and sort by the same field. When you use No Aggregation, you can select the sort Order of as Alphabetical (A-Z) or Reverse Alphabetical (Z-A).
LISTAGG	Use to transform row-level data into a string of consolidated data, comma-separated data, or other custom-delimited string. For example, use Listagg(fieldname, 'delimiter') to concatenate the fields values, separated by the defined delimiter. Pushdown is supported for multiple connectors: BigQuery, Impala, MySQL, MemSQL, Oracle, PostgreSQL, Redshift, Snowflake, and SparkSQL.

Suppose you have the following raw data:

Name	Gender	Age
Johnny	Male	10
Adam	Male	12
Mina	Female	11
Jenny	Female	13
Ann	Female	15

When this data is aggregated by gender, only two records (one for males and one for females) are returned and the aggregation must somehow determine what value to return for the age of the different genders. To do this, the aggregation requires input (using a metric function) about how the age should be returned. For example, if you elected to aggregate the data by gender and return the average age using the AVG metric function, the resulting data would be:

Gender	Age Returned	Aggregation Calculation	Aggregation Logic
Male	11	10 + 12 = 22 /2 = 11	Johnny’s and Adam’s ages are summed and divided by 2 (two males).
Female	13	11 + 13 + 15 = 39 /3 = 13	Mina’s, Jenny’s, and Ann’s ages are summed and divided by 3 (three females).

If you elected to aggregate the data by gender and return the minimum age using the MIN metric function, the resulting data would be:

Gender	Age Returned	Aggregation Logic
Male	10	Johnny’s and Adam’s ages are evaluated and the lower of the two ages is returned.
Female	11	Mina’s, Jenny’s, and Ann’s ages are evaluated and the lower of the three ages is returned.

LAST VALUE Examples

The LAST VALUE examples in this section use the following data:

Record #	Gender	Country	Price	Items	Sale_Date
1	Male	US	10	6	2019-01-03
2	Male	US	20	5	2019-01-02
3	Male	UK	30	4	2019-01-01
4	Male	UK	40	3	2019-01-01
5	Male	UA	50	2	2019-01-02
6	Male	UA	60	1	2019-01-03
7	Female	US	1	7	2019-01-04
8	Female	US	11	6	2019-01-03
9	Female	US	21	5	2019-01-02
10	Female	UK	31	4	2019-01-01
11	Female	UK	41	3	2019-01-01
12	Female	UA	51	2	2019-01-02
13	Female	UA	61	1	2019-01-03
14	Female	UA	71	0	2019-01-04

Examples: Grouping By One Field

Suppose you aggregate this data by Gender and request that the last value for Price be returned based on the Sale_Date. The results would be:

Gender	Price Returned	Aggregation Logic
Male	60	In all the records for males, two records have the latest date (2019-01-03) - records #1 and #6. Therefore, the prices in both records are compared and the maximum price is returned. The result is 60 from record #6.
Female	71	In all the records for females, two records have the latest date (2019-01-04) - records #7 and #14. Therefore, the prices in both records are compared and the maximum price is returned. The result is 71 from record #14.

Suppose you aggregate this data by Country and request that the last value for Price be returned based on the Sale_Date. The results would be:

Country	Price Returned	Aggregation Logic
US	1	In all the records for the US, the record with the latest date is for the female with a sale date of 2019-01-04 (record #7). The price in that record is 1.
UK	41	All the records for the UK are for 2019-01-01. Therefore, the prices in all records are compared and the maximum price is returned. The result is 41 from record #11.
UA	71	In all the records for the UA, the record with the latest date is for a female with a sale date of 2019-01-04 (record #14). The price in that record is 71.

Example: Grouping By Two Fields

Suppose you aggregate this data by Gender and then by Country and request that the last value for Price be returned based on the Sale_Date. The results would be:

Gender	Country	Price Returned	Aggregation Logic
Male	US	10	The two records for US males are compared and the latest record has a sale date of 2019-01-03 (record #1). The price in that record is 10.
Male	UK	40	The two records for UK males have the same sale dates (2019-01-01). Therefore, the prices in all UK male records are compared and the maximum price is returned. The result is 40 from record #4.
Male	UA	60	The two records for UA males are compared and the latest record has a sale date of 2019-01-03 (record #6). The price in that record is 60.
Female	US	1	The three records for US females are compared and the latest record has a sale date of 2019-01-04 (record #1). The price in that record is 1.
Female	UK	41	The two records for UK females have the same sale dates (2019-01-01). Therefore, the prices in all UK female records are compared and the maximum price is returned. The result is 41 from record #11.
Female	UA	71	The three records for UA females are compared and the latest record has a sale date of 2019-01-03 (record #1). The price in that record is 10.

Example: Grouping By Two LAST VALUE Metrics

Suppose you aggregate this data by Gender and request that the last value for Price and the last value for Items be returned based on the Sale_Date. The results would be:

Gender	Price Returned	Items Returned	Aggregation Logic
Male	60	6	In all the records for males, two records have the latest date (2019-01-03) - records #1 and #6. The prices and item counts in both records are compared and the maximum price and item count are returned. The returned results are a price of 60 from record #6 and 6 items from record #1.
Female	71	7	In all the records for females, two records have the latest date (2019-01-04) - records #7 and #14. The prices and item counts in both records are compared and the maximum price and item count are returned. The returned results are a price of 71 from record #14 and 7 items from record #7.

Supported SQL-Like Expressions

Self-Service Analytics’s custom metrics support the following SQL-like expressions:

Expression	Description
WHERE	Use WHERE to filter by a condition. Data will only be included in the custom metric if the condition that follows is true. For example: COUNT(weatherdelay) WHERE airportcode IN ('LAX', 'ORD', 'IAD') Row-level functions and expressions can be used in WHERE clauses in custom metrics. In a custom metric, WHERE clauses allow you to specify a formula without first creating a derived field. The WHERE clause must be in the leftmost part of the custom metric expression, but it can be expressed with a row-level function or any of the aggregate functions available for custom metrics. In the following example, the total planned sales is calculated for men. SUM(plannedsales) WHERE UPPER(gender) = 'MALE'
AND	Use AND to form a conjunctive condition. Data is only included in the custom metric if it meets both of the conditions connected by AND. The following example calculates the sum of deicing only if the broadphaseofflight includes LANDING and the airportcode is YYZ. SUM(deicing) WHERE broadphaseofflight IN 'LANDING' AND airportcode='YYZ'
OR	Use OR to for a disjunctive condition. Data is included in the custom metric if it meets either of the conditions connected by OR. The following example calculates the sum of deicing if the broadphaseofflight includes LANDING or the airportcode is YYZ. SUM(deicing) WHERE broadphaseofflight IN 'LANDING' OR airportcode='YYZ'
BETWEEN…AND	Use BETWEEN to filter using a range of values. The following example counts the number of distinct records for weatherdelay that have cancelledflight counts between 2 and 10. COUNTD(weatherdelay) WHERE cancelledflight BETWEEN 2 AND 10
IN	Use IN to filter using a set of values. Data is included in the custom metric only if a data field matches one of the listed values. The following example calculates the sum of weatherdelay only for records in which the airportcode field is LAX, ORD, or IAD. SUM(weatherdelay) WHERE airportcode IN ('LAX','ORD','IAD')
NOT IN	Use NOT IN to filter using a set of values. Data is included in the aggregation only if a data field does not match one of the listed values. The following example calculates the sum of weatherdelay only for records in which the airportcode field is not LAX, ORD, or IAD. SUM(weatherdelay) WHERE airportcode NOT IN ('LAX','ORD','IAD')
TRANSFORM	Use TRANSFORM to filter based on a derived date. To derive a date with TRANSFORM, you must already have a time attribute configured in your data source. The following example calculates the sum of weatherdelay only for records in which the eventdate is for the previous period. In other words, if the visual is examining two weeks of data for weatherdelay, this calculation will provide data about the two weeks prior to that. SUM(weatherdelay) TRANSFORM eventdate=PreviousPeriod() To work correctly, data must be available for the periods of time considered.

Row-Level Expressions

A row-level expression is a mathematical expression involving a single record (row) in the data. They are used to calculate derived fields but can also be used in custom metrics and admin-defined functions.

Row-level expressions are created using attributes and metrics from the data and row-level functions.

Be careful not to create row-level expressions using fields in a data source that have had their field data type changed. Doing so may generate errors for the row-level expression. Instead, use the original field (with its original field type) to create a derived field of the field type you need and then use the new derived field in your row-level expressions.

Operators

Valid operators used in Self-Service Analytics are described below.

Operator	Applies to	Determines whether the data in the field you select…
Begins With	strings	Begins with the string you have specified.
Between	numbers	Falls numerically between the two numbers you specified.
Contains	strings	Contains the string you have specified.
Does Not Begin With	strings	Does not begin with the string you have specified.
Does Not Contain	strings	Does not contain the string you have specified.
Does Not End With	strings	Does not end with the string you have specified.
Ends With	strings	Ends with the string you have specified.
Equal	numbers	Is equal to the number you specified.
Exclude	numbers & strings	Includes the number or string you specify. If it does, the data is excluded from the output.
Greater Than	numbers	Is greater than the number you specified.
Greater Than or Equal	numbers	Is greater than or equal to the number you specified.
Include	numbers & strings	Includes the number or string you specify. If it does, the data is included in the output.
Less Than	numbers	Is less than the number you specified.
Less Than or Equal	numbers	Is less than or equal to the number you specified.
Not Between	numbers	Does not fall numerically between the two numbers you specified.
Not Equal	numbers	Is not equal to the number you specified.

Distinct Counts

Distinct count functionality determines the number of unique values in a column or expression within a selected table by comparing all the records pulled from the data store by a data source configuration. When distinct counts are used, unique value results are returned when analyzing data. For example, distinct counts could return the number of:

• Unique customers in a sales database
• Unique UPC codes for a category of products
• The number of trucks in a company’s fleet

For example, given a single collection and string field with the following three values:

1. Apple
2. Orange
3. Apple

The distinct count returns 2, since there are only two distinct values (“Apple” and “Orange”), while an ordinary count returns 3 to reflect the total number of records. SQL-based connectors might produce a query that looks like this:

select count(distinct myField) from myCollection

Support for this feature by connector is shown in the following table.

Key:Y - Supported; N - Not Supported; N/A - not applicable

Connector	Supported?	Notes
Amazon Redshift	Y
Amazon S3	Y
Apache Drill	Y
Apache Phoenix	Y
Apache Phoenix Query Server (QS)	Y
Apache Solr	Y
BigQuery	Y	If you need to access a BigQuery partition, explicitly include an alias for the built in partition column in your select clause, such as select *, _PARTITIONTIME as pt from projectId.datasetId.tableId.
Business Central Jet	Y
Cloudera Impala	Y	Cloudera Impala connectors can receive only a single distinct count field in a query.
Cloudera Search	Y
Couchbase	Y
Dremio	Y
Dundas BI (Managed)	source-dependent
Elasticsearch 7.0	Y
Elasticsearch 8.0	Y
File Upload (Upload API)	Y
HDFS	Y
Hive	Y
Jira	Y
MemSQL	Y
Microsoft SQL Server	Y
MongoDB	Y
MySQL	Y
OpenSearch	Y
Oracle	Y
PostgreSQL	Y
Python	Y
Real Time Sales	Y
Salesforce	Y
SAP Hana	Y
SAP S/4HANA	Y
SAP IQ	Y
Spark SQL	Y
Snowflake	Y
Teradata	Y
TIBCO DV	Y
Trino	Y
File Upload (Upload API)	Y
Vertica	Y