Supported Functions 
This article explains the following supported functions in Angles Professional.
Row Level Functions
Use row-level functions in the row-level expressions to create the following calculation types:
- derived fields
- custom metrics
The row-level functions described in this section are supported by both calculation types.
Row-level functions are divided into the following categories:
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 in 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. |
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 the 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.
Time Functions
The following time functions are supported. Valid values for <timepart> vary, based on the 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.
Note: 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. |
|
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 the 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>) |
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 ) |
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) |
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. |
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 function is explained in the following sections:
Conditional CASE Expressions
You can incorporate CASE expressions, whether singular or nested, into your custom metrics, similar to how you use 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 then that 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.
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. |
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. |
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).
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.
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. |
|
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 |
|
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: 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.
Note: 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). It 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. |
Supported SQL-Like Expressions
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 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. |