Page tree
Skip to end of metadata
Go to start of metadata

The following MAQL expressions are displayed in the Aggregation, Logical, and Filters tabs in the Custom Metric Editor.

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

  • COUNT is an aggregation function, except that it counts the number of distinct values of an attribute.

    Function

    Description

    AVG

    Average of all values of a fact or metric. See AVG.

    RUNAVG

    Running average of all values of a fact or metric. See Running Total Functions.

    CORREL

    Correlation coefficient between two sets of values. See CORREL.

    COUNT

    Number of unique values in an attribute. See COUNT.

    COVAR

    Covariance between two sets of fact or metric values for sample populations. See Covariance Functions.

    COVARP

    Covariance between two sets of fact or metric values for biased populations. See Covariance Functions.

    FORECAST

    Calculates future trendline values using linear regression. See FORECAST - Add a Trend Line to a Report.

    INTERCEPT

    Y-intercept value for the least-squares linear regression. See Least-Squares Functions.

    MAX

    Maximum of all values of a fact or metric. See MAX.

    RUNMAX

    Running maximum of all values of a fact or metric. See Running Total Functions.

    MEDIAN

    Median value of all values of a fact or metric. See Running Total Functions.

    MIN

    Minimum of all values of a fact or metric. See MIN.

    GREATESTHighest value from facts or metrics within a row. See GREATEST
    LEASTLowest value from facts or metrics within a row. See LEAST

    RUNMIN

    Running minimum of all values of a fact or metric. See Running Total Functions.

    PERCENTILE

    Computes the kth percentage of a set of values, where k is specified as the second parameter. See PERCENTILE.

    RSQ

    The square of the correlation coefficient between two sets of values. See Least-Squares Functions.

    SLOPE

    The slope of the least-squares line for linear regression. See Least-Squares Functions.

    STDEV

    The standard deviation among a set of fact or metric values based on a sample of the population. See Standard Deviation Functions.

    STDEVP

    The standard deviation among a set of fact or metric values based for a biased population. See Standard Deviation Functions.

    RUNSTDEV

    The running standard deviation among a set of fact or metric values. See Running Total Functions.

    SUM

    Sum of all values of a fact or metric. See SUM.

    RUNSUM

    Running sum of all values of a fact or metric. See Running Total Functions.

    VAR

    Variance between two sets of values for a sample of the population. See Variance Functions.

    VARP

    Variance between two sets of values for a biased population. See Variance Functions.

    RUNVAR

    Running variance between two sets of values. See Running Total Functions.

    Numeric Functions

    Numeric functions perform mathematical operations on facts or metrics. These functions can be simple arithmetic operators or more sophisticated calculations.

  • Simple arithmetic operators: +  -  *  /
  • See Adding, Subtracting, Multiplying, and Dividing.

    Function

    Description

    ABS

    Absolute value of a fact or metric. See ABS.

    EXP

    Returns the constant e raised to the power of a fact or metric. See EXP.

    IFNULL

    Returns a replacement value if the fact or metric evaluates to a NULL value. See IFNULL.

    LOG

    Calculates the logarithm for a value, given the base specified in the second parameter. See LOG.

    LN

    Calculates the natural logarithm for a value. See LN.

    POWER

    Returns a fact or metric value raised to the value specified in the second parameter. See POWER.

    RANK

    Returns the numeric rank for fact or metric values in ascending, descending, default, or smallest-first order. See Rank.

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

    CEILING

    Rounds an input value to the nearest integer that is greater than it. See FLOOR and CEILING.

    TRUNC

    Truncates a number based on the number of decimal places specified in the second parameter. See TRUNC.

    SIGN

    Returns -1 for negative values, 0 for zero values, and 1 for positive values. See SIGN.

    SQRT

    Returns the square root of a value. Negative values generate a NULL value. See SQRT.

    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.

    Operators

    Description

    = (equals)

    See Equals Operator.

    <> (does not equal)

    See Does not equals operator.

    BETWEEN

    Computes metric for input values between a specified range, including or excluding endpoints. See BETWEEN.

    NOT BETWEEN

    Computes metric for input values outside of a specified range, including or excluding endpoints. See NOT BETWEEN.

    IN

    Computes metric filtered by multiple listed values of the specified attribute. See IN.

    NOT IN

    Computes metric by specified attribute, excluding the listed attribute values. See NOT IN.

    TOP

    Returns topmost values, as specified by a percentage or a numeric count, of the input. See Rank Variations.

    BOTTOM

    Returns bottommost values, as specified by a percentage or a numeric count, of the input. See Rank Variations.

    WITHOUT PARENT FILTER

    Computes metric ignoring any filters applied at any level above the metric (metric, report, or dashboard). See PARENT Filters.

    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

    Computes metric applying filters at all levels above the metric (metric, report, or dashboard), except for the specified filters. See PARENT Filters.

    WITH PARENT FILTER EXCEPT

    Computes metric ignoring filters all filters above the metric (metric, report, or dashboard), except for the specified filters. See PARENT Filters.

    THIS

    Floating reference to the current date-related attribute value. See THIS Macro.

    PREVIOUS

    Floating reference to the preceding date-related attribute value. See PREVIOUS Macro.

    NEXT

    Floating reference to the next date-related attribute value. See NEXT Macro.

    LIKEFilters for the string pattern and is case sensitive.
    ILIKEFilters for the string pattern and is case insensitive.
    NOT LIKEReturns anything not defined in the string pattern and is case sensitive.
    NOT ILIKEReturns anything not defined in the string pattern and is case insensitive.

    Granularity Keywords

    These keywords can be used to set the aggregation level for the MAQL expression.

    Keywords

    Description

    BY

    Locks the aggregation level of the fact or metric, as specified by the attribute that is listed after BY. See BY.

    BY ALL attributes

    Locks the aggregation level of the fact or metric at the highest possible level of aggregation in the attribute hierarchy. See BY ALL.

    BY ALL IN ALL OTHER DIMENSIONS

    Locks the value of the fact or metric at the highest possible aggregation level across all dimensions. See BY ALL IN ALL OTHER DIMENSIONS.

    By Attr, ALL IN ALL OTHER DIMENSIONS

    Locks the aggregation level for the attribute or attributes specified after the BY keyword and at the highest level for all other dimensions. See BY attribute ALL IN ALL OTHER DIMENSIONS.

    FOR Next

    Refers to the values from the next time period specified as the parameter. See FOR Next.

    For Previous

    Refers to the values from the previous time period specified as the parameter. See FOR Previous.

    For NextPeriod

    Refers to the values from the next time period, as specified by the most granular attribute level in the report. See FOR NextPeriod.

    For PreviousPeriod

    Refers to the values from the previous time period, as specified by the most granular attribute level in the report. See FOR PreviousPeriod.

    BY ALL IN ALL OTHER DIMENSIONS EXCEPT (FOR)

    Functions as the BY ALL IN ALL OTHER DIMENSIONSBY keyword, except for the attributes specified after EXCEPT. The metric is permitted to be sliced and diced by these attributes, as long as they are present in the report. See BY ALL IN ALL OTHER DIMENSIONS EXCEPT FOR attribute.

    WITHIN

    Specifies a sub-group in the report, which may respect or ignore the report dimensionality defined by report attributes. See Rank.

    ROWS BETWEENThis specifies a rolling window over which running total functions (for example RUNAVG) are computed. See Rolling Window.

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

    Operators

    Description

    AND

    If both expressions are true, then the result is true. Else, it is false. See AND.

    CASE

    Statement evaluates a series of conditions specified after a WHEN keyword. The first condition that evaluates to true forces the expression to return the result specified after the corresponding THEN keyword. See CASE.

    IF/THEN/ELSE

    Statement evaluates the condition specified after IF. If the result is true, the value expressed after THEN is returned. Otherwise, the value after ELSE (if present) is returned. See IF THEN ELSE.

    NOT

    However the expression evaluates, the opposite Boolean value is returned. If the expression is true, then the result is false, and vice-versa. See NOT.

    OR

    If either expression is true, then the result is true. Else, it is false. See OR.