You are viewing our older product's guide. Click here for the documentation of GoodData Cloud, our latest and most advanced product.
MAQL Expression Reference
The MAQL expression reference guide provides with you with a comprehensive list of MAQL functions, operators and keywords that are available in Aggregation, Numeric, Granularity, Logical, and Filters tabs in the Custom Metric Editor.
Aggregation
Aggregation functions are simple mathematical functions that can be performed on your facts to create metrics. An example metric returns the total amount won in sales by summing individual sales figures, which are stored as a fact.
| Function | Description |
|---|---|
| AVG | Returns the average value of all numbers in the set (e.g., Salary paid). Null values are ignored. See AVG. |
| RUNAVG | Running average of all values of a fact or metric. See Running Totals for Core Functions. |
| CORREL | Returns correlation coefficient that varies between -1 and 1. Correlation indicates the degree of association between two sets of values. See CORREL. |
| COUNT | Returns the number of unique values of an attribute within the given context. See COUNT. |
| COVAR/COVARP | Returns the statistical covariance - how much two sets of values change together based on a sample of the population (COVAR), or based on a biased population (COVARP). See Covariance Functions. |
| FORECAST | Returns linear regression estimates for up to 10 future periods based on existing data. Second parameter is number of periods; default is 3. See FORECAST - Adding a Trend Line. |
| MAX | Returns the maximum value of all numbers in the set (e.g., Salary Paid). |
| RUNMAX | Running maximum of all values of a fact or metric. See Running Totals for Core Functions. |
| MEDIAN | Counts the statistical median - an order statistic that gives the "middle" value of a sample. If the "middle" falls between two values, the function returns average of the two middle values. Null values are ignored. See Median. |
| MIN | Returns the minimum value of all numbers in the set (e.g., Salary Paid). See MIN. |
| RUNMIN | Running minimum of all values of a fact or metric. See Running Totals for Core Functions. |
| PERCENTILE | Returns the k-th percentile of values in a range - k is specified in the second parameter by a decimal number or a percentage. See PERCENTILE. |
| RSQ | Returns square of the correlation coefficient. See Least-Squares Functions. |
| STDEV | Returns the statistical standard deviation of all values in the set based on a sample of the population (STDEV), or based on a biased population (STDEVP). See Standard Deviation Functions. |
| RUNSTDEV | The running standard deviation among a set of fact or metric values. See Running Total Statistical Functions. |
| SUM | Returns a sum of all numbers in the set (e.g., Salary Paid). See SUM. |
| RUNSUM | Running sum of all values of a fact or metric. See Running Totals for Core Functions. |
| VAR/VARP | Returns the statistical variance - how far a set of values is spread out based on a sample of the population (VAR), or based on a biased population (VARP). Variance is non-negative, zero indicates that all values are identical. See Variance Functions. |
| RUNVAR | Running variance between two sets of values. See Running Total Statistical Functions. |
Numeric
Numeric functions perform mathematical operations on facts or metrics. These functions can be simple arithmetic operators (see Arithmetic Operations) or more sophisticated calculations.
| Function | Description |
|---|---|
| +, -, *, / | Computes simple arithmetic expressions. Division by zero returns a NULL value. See Arithmetic Operations. |
| ABS | Returns an absolute value of a number (e.g., Revenue). See ABS. |
| EXP | Returns e raised to the power of a number (e.g., Annual rate). See EXP. |
| IFNULL | Returns a replacement value, specified in the second parameter of the IFNULL function, in place of the NULL value. When used in an expression an outer-join is applied on the data level. See IFNULL. |
| LOG | LOG returns the logarithm of a number for the given base specified in second parameter of the LOG function. If the base value is omitted, base 10 is used. See LOG. |
| LN | LN returns the natural logarithm of a number. The function returns NULL if number is less than or equal to 0. See LN. |
| POWER | Returns a number raised to the base specified as the second parameter of the POWER function (e.g., Interest rate, Revenue). Complex and undefined results are replaced with NULL. See POWER. |
| RANK | Returns a numerical rank for metric values in ascending, default, order (the smallest value ranked first) or descending order (the largest value ranked first). See Rank. Note: See also WITHIN Clause. |
ROUND | Rounds a fact or metric to the number of decimal places specified in the second parameter. See ROUND. |
FLOOR | Rounds an input value to the nearest integer that is less than it. See FLOOR. |
CEILING | Rounds an input value to the nearest integer that is greater than it. See CEILING. |
TRUNC | Truncates a number based on the number of decimal places specified in the second parameter. See TRUNC. |
| SIGN | Returns -1 if the number is negative, 0 if the number is zero, or 1 if the number is positive. See SIGN. |
| SQRT | Returns a square root of a number (e.g., Revenue), if negative then returns NULL. See SQRT. |
* Round and truncate functions accept one or two parameters, where the second is the number of decimal places (may be negative) – if omitted, number is rounded/truncated to the nearest integer. A NULL value in any of the rounding function returns NULL
Granularity Keywords
The following granularity keywords can be used to set the aggregation level for the MAQL expression. If your metric contains multiple keywords, they must be listed in alphabetical order.
| Keyword | Description |
|---|---|
| BY | This locks the value of the metric at the aggregation level specified by the attribute after the BY statement. Multiple attributes separated by commas from different dimensions can be specified. See BY. |
| BY ALL attributes | Let is say we would like to create showing percentage of Resolution time not just out of the Quarter or Year, but all time. Since the highest-level attribute in the date dimension is Year, we need a new construct BY ALL Year. See BY ALL. |
| BY ALL IN ALL OTHER DIMENSIONS | This locks the value of the metric at the highest possible aggregation level across all dimensions; therefore, this returns a grand total that is indivisible. BY ALL IN ALL OTHER DIMENSIONS. |
| BY Attribute ALL IN ALL OTHER DIMENSIONS | This complements the BY statement by specifying how the aggregation of the metrics should be calculated in all other dimensions not previously specified in the BY statement. Omitting ALL IN ALL OTHER DIMENSIONS allows the metric to be sliced and diced in other dimensions. See BY attribute ALL IN ALL OTHER DIMENSIONS. |
| FOR Next | By default the function refers to values from one time period in the future. This can be customized by adding a second parameter to the function. See FOR Next. |
| FOR Previous | By default the function refers to values from one time period ago. This can be customised by adding a second parameter to the function. See FOR Previous. |
| FOR NextPeriod | Functions similarly as FOR Next function unless the adaptable functions adapt to the context of the report by hand. Even though the metric is defined as FOR NextPeriod(Quarter), for example, the function returns values from the next month, as month is the most granular date attribute in this report. See FOR NextPeriod. |
| FOR PreviousPeriod | Functions similarly as FOR Next and FOR Previous functions unless the adaptable functions adapt to the context of the report by hand. Even though the metric is defined as FOR PreviousPeriod(Quarter), for example, the function returns values from the previous month, as month is the most granular date attribute in this report. See FOR PreviousPeriod. |
| BY ALL IN ALL OTHER DIMENSIONS EXCEPT (FOR) | Adding "EXCEPT FOR attribute" to the BY ALL clause specifies an exception where the metric will be sliced and diced by the specified attribute (and its hierarchy) if the attribute is contained in the report. See BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR attribute. |
| WITHIN | Creates so called subgroups respecting or ignoring the report dimensionality specified in HOW part on the report creation page. See WITHIN Clause. |
| ROWS BETWEEN | This specifies a rolling window over which running total functions (for example RUNAVG) are computed. See Rolling Window. |
| OVER...TO | OVER...TO syntax can reduce the number and complexity of data permissions in your logical data model. SeeAdvanced Data Permissions Use Cases. |
Logical Operators
The following logical operators can be used to combine filters and apply conditions to your MAQL expressions.
| Operator | Description |
|---|---|
| AND | If both expressions are true (eg. not FALSE and not NULL), then the result is true. In all other cases, the result is false If you combine filters using AND, both filters are applied when computing the metric. See AND. |
| CASE | CASE is a conditional statement that performs logical tests on a series of conditions and returns values when a condition is met. See CASE. |
| IF/THEN/ELSE | Statement returns one of two possible values, or performs one of two possible computations, based on the condition met. Since the statement is used inside a metric, only use numerical values in the THEN part of the statement. See IF THEN ELSE. |
| NOT | If you specify a filter and precede it with NOT, then everything that is specified by the filter is excluded from the computation. See NOT. |
| OR | If either expression is true, then the result is true. If both expressions are false, then the result is false. If you combine filters using OR, then the result is computed individually for each filter and the results are combined. See OR. |
Filter Expressions
Filters allow you to narrow the set of data from which a metric is computed by targeting attribute values you wish to include or exclude from consideration.
For example, a metric showing sales from a particular region.
| Operator | Description |
|---|---|
| = (equals), <> (does not equal), > (greater), < (less), >= (greater or equal), <= (less or equal) | See Equals Operator, Does Not Equal Operator, and Other Relational Operators. |
| BETWEEN | Computes the metric by using attribute values, metrics or macros, from a specified range (including/excluding endpoints). See BETWEEN. |
| NOT BETWEEN | Computes the metric by using attribute values, metrics or macros, outside of a specified range (including/excluding endpoints). See NOT BETWEEN. |
| IN | Computes the metric from multiple attribute values. See IN. |
| NOT IN | Computes metric by specified attribute, excluding the listed attribute values. See NOT IN. |
| TOP (%) | Returns the members that fit in the specified condition defined as a metric. The number of members that fits in the output set could be specified by a number or a percentage. Based on metric values. TOP(3) condition returns all the members that fits in his condition, that could be more than three rows. See Extended TOP and BOTTOM Ranks. |
| BOTTOM (%) | Returns the members that fit in the specified condition defined as a metric. The number of members that fits in the output set could be specified by a number or a percentage. Based on metric values. BOTTOM (3) condition returns all the members that fits in his condition, that could be more than three rows. See Extended TOP and BOTTOM Ranks. |
| WITH PARENT FILTER | Computes metric applying filters at all levels above the metric (metric, report, or dashboard). This is the default behavior. See PARENT Filters. |
| WITH PARENT FILTER EXCEPT | Includes all parent filters, except for any filters specified as a comma-separated list of attributes. Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. See PARENT Filters. |
| WITHOUT PARENT FILTER | Computes the metric with no regard to any filter specified the metric. Note: This clause allows the computation of an amount (e.g., sum) for all Years even if some Years were filtered out of the parent metric, report, or dashboard. See PARENT Filters. |
| WITHOUT PARENT FILTER EXCEPT | Removes parent filters, except for any filters specified as a comma-separated list of attributes. Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. See PARENT Filters. |
| THIS | The THIS macro references the current value for the specified attribute of the Date dimension. See THIS Macro. |
| PREVIOUS | The PREVIOUS macro is used to select the attribute value for the date dimension that immediately precedes the current value. See PREVIOUS Macro. |
| NEXT | The NEXT macro is used to select the attribute value for the date dimension that immediately follows the current value. See NEXT Macro. |
| LIKE, NOT LIKE, ILIKE, NOT ILIKE | This condition filters an attribute-label string value matching a specified pattern. The pattern can contain one or more wildcard characters. ILIKE is equivalent to LIKE except that the match is not case-sensitive. An underscore (_) in a pattern matches any single character; a percent sign (%) matches any number of characters. The pattern must be enclosed in double-quotes (""). See Filtering with the LIKE Clause |