AQL user-defined functions

Overview

A user-defined function (UDF) is a reusable AQL expression with typed parameters. You declare it in AML and call it from any metric or dimension body, the same way you call a built-in like safe_divide().

Without UDFs, a shared rule such as a tax rate or a weighted average is copied into every metric that needs it. A change means editing every copy. With a UDF you write the rule once, and one edit updates every metric that calls it.

Metrics that call a UDF stay editable in the UI, and the editor shows autocomplete for the function and its parameters.

Key capabilities

User-defined functions bring reusability and maintainability to your analytics workflow. Here are the key capabilities that make them powerful:

• Define once, call anywhere. Call a function from any metric or dimension in a dataset that imports its library.
• Typed contract. Each parameter has a type, and the function declares its return_type.
• Re-scoping parameters. A metric, dimension or model parameter is passed to the function unevaluated, so the function can filter or regroup it and get the correct result.
• Shareable. A function is an AML object, so you can group functions into a library with AqlFunctionModule and reuse it across projects.

What you can build

Each template below is a complete, ready-to-use function library.

• Box plot in a table: show the spread of a measure as a box plot in each table row.
• Progress bar in a table: show a value against a target as a progress bar in each table row.
• Line chart in a table: show the trend of a measure over time as a line chart in each table row.
• ABC classification: classify items into A, B and C by their share of a total, such as products by sales.
• Project profitability: calculate discount factor, cash flows, NPV and payback year for a project.

Define a function library

A function lives inside an AqlFunctionModule, the library that holds it.

You can define a library in any .aml file in your project. We recommend keeping all libraries in one folder, library/aql-functions/, with one file per library, so functions are easy to find and reuse:

library/
  aql-functions/
    pricing.aml
    svg.aml
    abc.aml

library/aql-functions/pricing.aml

AqlFunctionModule Pricing {
  function AddTax {
    param pAmount {
      type: 'number'
    }
    return_type: 'number'
    definition: @aql pAmount * 1.1 ;;
  }
}

• AqlFunctionModule: the library. A named container for functions, like a model or a dataset.
• function: one reusable AQL expression.
• param: an input with a type. All parameters are required and positional.
• mode: optional, 'val' (default) or 'expr'. See Parameter mode.
• return_type: the type the function returns.
• definition: the AQL expression. It can use the parameter names.

Group functions into modules

A library can hold nested modules to group related functions. A nested module is also declared as an AqlFunctionModule.

AqlFunctionModule Pricing {
  function AddTax { ... }

  module Filter: AqlFunctionModule {
    function ComputeForRed { ... }
  }
}

Import a library into a dataset

Import the library with function_module. Every function inside becomes callable in that dataset.

Dataset ecommerce {
  models: [orders, products]

  function_module Pricing: Pricing
}

The name on the left is the name this dataset uses. The name on the right is the library.

Call a function

Call a function from any @aql body in the dataset by its path: library, then module, then function.

metric total_with_tax {
  type: 'number'
  definition: @aql Pricing.AddTax(sum(orders.total)) ;;
}

Parameter types

When defining a user-defined function, you must specify the type of each parameter to ensure the function receives the correct input. The table below lists the supported parameter types and how they are passed to the function:

Type	Accepts	Passed as
number, text, date, datetime, truefalse	a value	a value
dimension	a field, such as products.color	unevaluated
metric	an aggregation, such as sum(orders.value)	unevaluated
model	a model, such as products	unevaluated

Pass a metric when the function re-filters it

If the function body filters or regroups an input, declare that parameter as metric. A number parameter is computed before the body runs, so a filter in the body does not change it.

AqlFunctionModule Pricing {
  module Filter: AqlFunctionModule {
    function ComputeForRed {
      param pAmount {
        type: 'metric'
      }
      param pProductColor {
        type: 'dimension'
        mode: 'expr'
      }
      return_type: 'number'
      definition: @aql pAmount | where(pProductColor == 'Red') ;;
    }
  }
}

metric red_sales {
  type: 'number'
  definition: @aql Pricing.Filter.ComputeForRed(sum(orders.value), products.color) ;;
}

Here sum(orders.value) runs on red products only. With a number parameter, the total would be computed first and the filter would have no effect.

Parameter mode

mode sets when an argument is computed.

• val (default): the argument is computed once, at the call site, before the body runs. Every use of the parameter in the body sees the same value.
• expr: the argument is passed unevaluated. It is computed each time the body uses it, in the scope active at that point, such as inside where() or unique() | select(). Different uses can give different values.

Use mode: 'expr' when the body computes the input in its own scope. This function returns the median of a measure across the values of a field:

AqlFunctionModule Stats {
  function Median {
    param pAxisRef {
      type: 'dimension'
      mode: 'expr'
    }
    param pMeasureRef {
      type: 'number'
      mode: 'expr'
    }
    return_type: 'number'
    definition: @aql
      unique(pAxisRef)
        | select(x: pMeasureRef)
        | percentile_cont(x, 0.5)
        | of_all(pAxisRef)
    ;;
  }
}

metric median_product_sales {
  type: 'number'
  definition: @aql Stats.Median(products.id, sum(order_items.quantity)) ;;
}

pMeasureRef must be computed once per product, inside select(). With val, it would be computed once for the whole row before the body runs, and the median would be that single value.

When to use which

Take a function that returns the share of an amount that comes from red products:

function RedShare {
  param pAmount {
    type: 'number'
    mode: 'expr'
  }
  return_type: 'number'
  definition: @aql safe_divide(pAmount | where(products.color == 'Red'), pAmount) ;;
}

Call it with RedShare(sum(orders.value)), by product category:

Category	mode: 'val'	mode: 'expr'
Shoes	100%	23%
Bags	100%	41%

• With val, sum(orders.value) is computed once, before the body runs. The numerator and the denominator are the same total, so the result is always 100%.
• With expr, each use is computed where it appears. The numerator runs inside where(), so it counts red products only.

If the body changes the scope the input is computed in, use expr. Otherwise keep the default, val.

How it works

At compile time, each call is replaced by the function body with the arguments filled in. The result compiles like hand-written AQL, so a metric that calls a function behaves like any other metric.

Limitations

While user-defined functions are highly flexible, there are some limitations to keep in mind when designing your functions:

• Functions return a single value. Table return types are not supported.
• Recursion, overloading, optional parameters, named arguments and variadic parameters are not supported.
• Functions are defined at the module level (inside an AqlFunctionModule). You cannot define a function inside a query.