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, Logical, and Filters tabs in the Custom Metric Editor.

Contents:

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.

FunctionDescription
AVGReturns the average value of all numbers in the set (e.g., Salary paid). Null values are ignored. See AVG.
RUNAVGRunning average of all values of a fact or metric. See Running Totals for Core Functions.
CORRELReturns correlation coefficient that varies between -1 and 1. Correlation indicates the degree of association between two sets of values. See CORREL.
COUNTCounts unique values of a selected attribute (e.g., Employee) in a given dataset determined by the second attribute parameter (e.g., Records of Department). See COUNT.
COVAR/COVARPReturns 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.
FORECASTReturns linear regression estimates for up to 10 future periods based on existing data. Second parameter is number of periods; default is 3. See FORECAST - Add a Trend Line to a Report.
MAXReturns the maximum value of all numbers in the set (e.g., Salary Paid).
RUNMAXRunning maximum of all values of a fact or metric. See Running Totals for Core Functions.
MEDIANCounts 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.
MINReturns the minimum value of all numbers in the set (e.g., Salary Paid). See MIN.
RUNMINRunning minimum of all values of a fact or metric. See Running Totals for Core Functions.
PERCENTILEReturns 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.
RSQReturns square of the correlation coefficient. See Least-Squares Functions.
STDEVReturns 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.
RUNSTDEVThe running standard deviation among a set of fact or metric values. See Running Total Statistical Functions.
SUMReturns a sum of all numbers in the set (e.g., Salary Paid). See SUM.
RUNSUMRunning sum of all values of a fact or metric. See Running Totals for Core Functions.
VAR/VARPReturns 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.
RUNVARRunning 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.

FunctionDescription
+, -, *, /Computes simple arithmetic expressions. Division by zero returns a NULL value. See Arithmetic Operations.
ABSReturns an absolute value of a number (e.g., Revenue). See ABS.
EXPReturns e raised to the power of a number (e.g., Annual rate). See EXP.
IFNULLReturns 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.

LNLN returns the natural logarithm of a number. The function returns NULL if number is less than or equal to 0. See LN.
POWERReturns 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.
RANKReturns 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.
SIGNReturns -1 if the number is negative, 0 if the number is zero, or 1 if the number is positive. See SIGN.
SQRTReturns 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.

KeywordDescription
BYThis 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 attributesLet 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 DIMENSIONSThis 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 DIMENSIONSThis 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 NextBy 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 PreviousBy 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 NextPeriodFunctions 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 PreviousPeriodFunctions 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.
WITHINCreates so called subgroups respecting or ignoring the report dimensionality specified in HOW part on the report creation page. See WITHIN Clause.
ROWS BETWEENThis specifies a rolling window over which running total functions (for example RUNAVG) are computed. See Rolling Window.
OVER...TOOVER...TO syntax can reduce the number and complexity of data permissions in your logical data model. See Advanced Data Permissions Use Cases.

Logical Operators

The following logical operators can be used to combine filters and apply conditions to your MAQL expressions.

OperatorDescription
ANDIf 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.
CASEPerforms logical tests and returns appropriate values. The CASE function evaluates expression, compares it to a sequence of values and returns a numeric result. When a value that matches expression is found, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned. See CASE.
IF/THEN/ELSEStatement 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.
NOTIf you specify a filter and precede it with NOT, then everything that is specified by the filter is excluded from the computation. See NOT.
ORIf 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.

OperatorDescription
= (equals), <> (does not equal), > (greater), < (less), >= (greater or equal), <= (less or equal)See Equals Operator, Does Not Equal Operator, and Other Relational Operators.
BETWEENComputes the metric by using attribute values, metrics or macros, from a specified range (including/excluding endpoints). See BETWEEN.
NOT BETWEENComputes the metric by using attribute values, metrics or macros, outside of a specified range (including/excluding endpoints). See NOT BETWEEN.
INComputes the metric from multiple attribute values. See IN.
NOT INComputes 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 FILTERComputes metric applying filters at all levels above the metric (metric, report, or dashboard). This is the default behavior. See PARENT Filters.
WITH PARENT FILTER EXCEPTIncludes 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 FILTERComputes 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 EXCEPTRemoves 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.
THISThe THIS macro references the current value for the specified attribute of the Date dimension. See THIS Macro.
PREVIOUSThe PREVIOUS macro is used to select the attribute value for the date dimension that immediately precedes the current value. See PREVIOUS Macro.
NEXTThe 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 ILIKEThis 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