# 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.

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. |