Get Started with Using MAQL to Write Metrics

In this article, you will learn how to use MAQL to build a series of simple metrics using Custom Metric Editor, a powerful GUI tool that allows you to construct metrics without typing syntax manually.

For examples of MAQL usage, see MAQL Use Cases and Tutorials.

Before You Start

This section is for users of both the Pixel Perfect and the Responsive user interface (see GoodData Portal) which use different naming conventions.

For the comprehensive overview of GoodData terminology, see GoodData Glossary.

MAQL and Metrics

MAQL is a query language that you use to build metrics - aggregations of the underlying data that produce a number.

Before you get started, you should know that:

• Metrics always return numerical values.
• Metric can only return value based on some dimensionality/context in which it is executed - more about that in MAQL and Multidimensionality.
• Metrics in MAQL start with keyword SELECT.  A simple aggregation function looks like this: SELECT SUM(Quantity)  where Quantity is a fact.

workspace elements in your MAQL definitions are automatically color-coded so you can keep track of which elements you included in your metric and ensure that your MAQL syntax is valid:

SELECT Quota-(Expected+Won) WITHOUT PF

• Facts
• Metrics
• Attributes
• Attribute Values
• Variables

Custom Metric Editor

The Custom Metric Editor is a graphical user interface tool that guides you through creating your own metrics in MAQL

How to Access the Custom Metric Editor

You can access the Custom Metric Editor through the Manage tab.

Or you can also access the Custom Metric Editor through the Reports pane.

Navigate through the following path - Create Report > What pane > Metrics column > Click (advanced). 

Steps:

1. On the Manage tab, navigate through the following path - Data Tab > Metrics > click Create Metric: 

   
   
    The Metric Editor appears.

2. Click Custom metric. 

   

    Custom Metric Editor appears.

3. Define your metric by entering MAQL statements directly into a text field or selecting them from the right-hand side menu. 

   
   

Working with Custom Metric Editor

You can type MAQL syntax manually into the main text field, or you can select MAQL functions from the bottom section of the Advanced Metric Editor.

Grouped in Aggregation, Numeric, Granularity, Logical, and Filters tabs, there are MAQL operators, functions, and keywords that you can click to use in your metric definitions. Each function also features a usage example to help you create your metrics. For more information, see MAQL Expression Reference .

On the right-hand side, you find the Element Sidebar that lists the workspace’s elements (facts, metrics, attributes, attribute values, and variables) - all in the form of snippets that you can insert directly into your metric’s definition.

To add the element into your metric, simply expand the desired element type, select the element, and double-click it or click Add Selected.

Clicking the colored ellipses opens the workspace element sidebar to the appropriate directory. From there, you can select the workspace element should be inserted into the syntax.

Write Your First Metrics

The following examples will show you a simple progression path to building your own metrics. Each example is followed by syntax description.

Example 1 - quantity

The following sample uses the SUM aggregation function on a fact (quantity) to return total all-time sales:

SELECT SUM(quantity)
SELECT SUM( fact )

This will result in a report featuring a single number, total units sold, in a single row.

Example 2 - adding arithmetic

To find out what was the revenue of the total all-time sales, first multiply two facts (quantity and price) and then aggregate to the context:

SELECT SUM(quantity*price)
SELECT SUM( fact * fact )

This will result in a report featuring a single number, revenue for total units sold, in a single row.

Example 3 - conditioning/filtering and reusing metrics

The following example reuses the existing metric (Amount Sold) and combines it with the conditional statement WHERE defined by the Attribute (Color) and Attribute Value (Red).

SELECT Amount Sold WHERE Color=red
SELECT Metrics WHERE Attribute=Attribute Value

When constructing complex metrics, your choice of attributes and attribute values is determined by the context of available data.

Add Comments to Custom Metric

You can add comments to your customized metric by including the # symbol.

Syntax:

# this is a comment

Any line that starts with that hash sign # is omitted from the computation. If you need multiline comments, each line must start with a #.