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 Type	Purpose
Table Functions	Transform an input table expression into an output table result
Metric Functions	Transform an input metric expression into another metric expression
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,...).
Logical Functions	Return value based on some logical conditions such as and, or, not, in, etc.
Time Intelligence Functions	Process date/time value such as truncating a timestamp or extracting a subfield from a date or time value
Null/Zero Handling Functions	Make working with null and zero values easier
Miscellaneous Functions	Other functions that don't fit into the above categories

Table Functions

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

Function	Syntax	Purpose
select	select(table, field)	Selects the fields to be returned in the query result.
group	group(table, field)	Groups the result set by the specified field(s).
filter	filter(table, field)	Filters and return the results that satisfy the conditions
unique	unique(table, field)	Returns the unique values of the specified field(s).

Metric Functions

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

Function	Syntax	Purpose
where	where(measure, condition)	Apply condition(s) to original measure
eval	eval(measure, metric_function)	Apply metric function(s) to modify the original measure
exclude, exclude_grains, of_all	exclude_grains(measure, model, dim) of_all(expression, model, dim)	Exclude dimension(s) from a measure calculation
dimensionalize	dimensionalize(measure, model, dim)	Choose exact dimension(s) for this measure calculation
with_relationships	with_relationships(measure, relationship)	Specifies what existing relationship(s) to used for this measure calculation
exact_period	exact_period(measure, time_dimension, time_range)	Returns the same measure calculated in a custom period.
relative_period	relative_period(measure, time_dimension, interval)	Returns the same measure calculated with shifting time
running_total	running_total(measure, running_dimension)	Returns a running total measure
rank	rank(order, partition)	Returns a rank measure
period_to_date	period_to_date(measure, date_part, date_dimension)	Returns a cumulative period to date measure
trailing_period	trailing_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,...).

Function	Syntax	Purpose
count	count(field)	Counts the total number of items in a group, not including NULL values
count_if	count_if(condition)	Count total rows from one table that are satisfied the condition
count_distinct	count_distinct(field)	Counts the total number of distinct items in a group, not including NULL values.
average	average(field)	Averages the values of items in a group, not including NULL values.
min	min(field)	Computes the item in the group with the smallest numeric value.
max	max(field)	Computes the item in the group with the largest numeric value.
sum	sum(field)	Sums the total number of items in a group, not including NULL values.
median	median(field)	Computes the median of an expression, which is the value that the values in the expression are below 50% of the time.
stdev	stdev(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.
stdevp	stdevp(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.
var	var(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.
varp	varp(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

Function	Syntax	Purpose
case when	case(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).
and	and(condition1, condition2, [,...])	compares between two Booleans as expression and returns true when both expressions are true.
or	or(condition1, condition2, [,...])	compares two Booleans as expression and returns true when one of the expressions is true.
not	not(field)	takes a single Boolean as an argument and invert it.
is	is(field)	evaluates the given statement and returns either True or False.
in	in(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.

Function	Syntax	Purpose
epoch	epoch(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_trunc	date_trunc(datetime, datetime_part)	Truncate a datetime to the granularity of datetime_part
day	day()	Truncate a datetime to the day granularity
month	month()	Truncate a datetime to the month granularity
year	year()	Truncate a datetime to the year granularity
quarter	quarter()	Truncate a datetime to the quarter granularity
week	week()	Truncate a datetime to the week granularity
hour	hour()	Truncate a datetime to the hour granularity
minute	minute()	Truncate a datetime to the minute granularity

Null/Zero Handling Functions

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

Function	Syntax	Purpose
coalesce	coalesce(value1, value2, [,...])	returns the first non-null value in a list
nullif	nullif(expr1, expr2)	returns NULL if two expressions are equal, otherwise, it returns the first expression.
safe_divide	safe_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.

Function	Syntax	Purpose
cast	cast(value, type)	Converts a value to a specified data type.
concat	concat(value1, value2, [,...])	Concatenates two or more strings.
is_at_level	is_at_level(dimension)	Returns true if the LoD context contains the specified dimension.