Skip to main content

AQL Functions Overview

AQL functions are the main building blocks of an AQL expression. They transform an input into an output based on specified arguments. They are typically combined using the AQL pipe operator.

Here are the main function types of AQL:

Function TypePurpose
Table FunctionsTransform an input table expression into an output table result
Metric FunctionsTransform an input metric expression into another metric expression
Aggregation FunctionsGroup values of multiple rows into a single summary value. They are equivalent to aggregation functions that SQL supports (SUM, COUNT, AVG, MAX, MIN,...).
Logical FunctionsReturn value based on some logical conditions such as and, or, not, in, etc.
Time Intelligence FunctionsProcess date/time value such as truncating a timestamp or extracting a subfield from a date or time value
Null/Zero Handling FunctionsMake working with null and zero values easier
Miscellaneous FunctionsOther functions that don't fit into the above categories

Table Functions

Table functions transform an input table expression into an output table result.

FunctionSyntaxPurpose
selectselect(table, field)Selects the fields to be returned in the query result.
groupgroup(table, field)Groups the result set by the specified field(s).
filterfilter(table, field)Filters and return the results that satisfy the conditions
uniqueunique(table, field)Returns the unique values of the specified field(s).

Metric Functions

Metric functions transform an input metric expression into another metric expression.

FunctionSyntaxPurpose
wherewhere(measure, condition)Apply condition(s) to original measure
evaleval(measure, metric_function)Apply metric function(s) to modify the original measure
exclude, exclude_grains, of_allexclude_grains(measure, model, dim)

of_all(expression, model, dim)
Exclude dimension(s) from a measure calculation
dimensionalizedimensionalize(measure, model, dim)Choose exact dimension(s) for this measure calculation
with_relationshipswith_relationships(measure, relationship)Specifies what existing relationship(s) to used for this measure calculation
exact_periodexact_period(measure, time_dimension, time_range) Returns the same measure calculated in a custom period.
relative_periodrelative_period(measure, time_dimension, interval)Returns the same measure calculated with shifting time
running_totalrunning_total(measure, running_dimension)Returns a running total measure
rankrank(order, partition)Returns a rank measure
period_to_dateperiod_to_date(measure, date_part, date_dimension)Returns a cumulative period to date measure
trailing_periodtrailing_period(measure, date_dimension, periods)Returns a trailing period measure

Aggregation Functions

Aggregation functions group values of multiple rows into a single summary value. They are equivalent to aggregation functions that SQL supports (SUM, COUNT, AVG, MAX, MIN,...).

FunctionSyntaxPurpose
countcount(field)Counts the total number of items in a group, not including NULL values
count_ifcount_if(condition)Count total rows from one table that are satisfied the condition
count_distinctcount_distinct(field)Counts the total number of distinct items in a group, not including NULL values.
averageaverage(field)Averages the values of items in a group, not including NULL values.
minmin(field)Computes the item in the group with the smallest numeric value.
maxmax(field)Computes the item in the group with the largest numeric value.
sumsum(field)Sums the total number of items in a group, not including NULL values.
medianmedian(field)Computes the median of an expression, which is the value that the values in the expression are below 50% of the time.
stdevstdev(field)Returns the standard deviation (sample) of the column created by expression unless expression defines a column of lists, in which case returns the standard deviation (sample) of each list.
stdevpstdevp(field)Returns the standard deviation (population) of the column created by expression unless expression defines a column of lists, in which case returns the standard deviation (population) of each list.
varvar(field)Returns the variance (sample) of the column created by expression unless expression defines a column of lists, in which case returns the variance (sample) of each list.
varpvarp(field)Returns the variance (population) of the column created by expression unless expression defines a column of lists, in which case returns the variance (population) of each list.

Logical Functions

Logical Functions return value based on some logical conditions such as and, or, not, in, etc.

info

Except for case when function, the rest of the logical functions are only available in Business Calculation due to legacy usage. Logical slice-and-dice can also be achieved by using AQL Operator

FunctionSyntaxPurpose
case whencase(when: condition, then: value, else: value)goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement).
andand(condition1, condition2, [,...])compares between two Booleans as expression and returns true when both expressions are true.
oror(condition1, condition2, [,...])compares two Booleans as expression and returns true when one of the expressions is true.
notnot(field)takes a single Boolean as an argument and invert it.
isis(field)evaluates the given statement and returns either True or False.
inin(field, value1, value2, [,...])takes a field expression and a list of values. Return true if that list of values contains the value of that field expression.

Time Intelligence Functions

Time intelligence functions process date/time value such as truncating a timestamp or extracting a subfield from a date or time value.

FunctionSyntaxPurpose
epochepoch(date); epoch(datetime)Returns a Unix timestamp which is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC
date_truncdate_trunc(datetime, datetime_part)Truncate a datetime to the granularity of datetime_part
dayday()Truncate a datetime to the day granularity
monthmonth()Truncate a datetime to the month granularity
yearyear()Truncate a datetime to the year granularity
quarterquarter()Truncate a datetime to the quarter granularity
weekweek()Truncate a datetime to the week granularity
hourhour()Truncate a datetime to the hour granularity
minuteminute()Truncate a datetime to the minute granularity

Null/Zero Handling Functions

Null/Zero handling functions make working with null and zero values easier.

FunctionSyntaxPurpose
coalescecoalesce(value1, value2, [,...])returns the first non-null value in a list
nullifnullif(expr1, expr2)returns NULL if two expressions are equal, otherwise, it returns the first expression.
safe_dividesafe_divide(value1, value2)Equivalent to the division granulity operator (X / Y), but returns NULL if an error occurs, such as a division by zero error.

Miscellaneous Functions

Other functions that don't fit into the above categories.

FunctionSyntaxPurpose
castcast(value, type)Converts a value to a specified data type.
concatconcat(value1, value2, [,...])Concatenates two or more strings.
is_at_levelis_at_level(dimension)Returns true if the LoD context contains the specified dimension.

Let us know what you think about this document :)