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

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.

If you are already a GoodData customer, enroll for the GoodData University MAQL training free of charge:

GoodData University

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

If you are not familiar with the usage of GoodData terminology, see GoodData Glossary.

Contents:

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.

Project 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 page or the Reports page.

Steps:

  1. Navigate to:
    • Manage page
      Navigate through the following path - Data Tab > Metrics > click Create Metric:
    • Reports Page
      Navigate through the following path - Create Report > What pane > Metrics column > Click (advanced).
       
      In both cases, 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 Project Element Sidebar that lists the project'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.

You can type MAQL syntax directly into the Metric Editor text field and you can also copy/paste syntax elements from other metrics. However, you must use the Project Element Sidebar to add project elements like facts, metrics, attributes, attribute values, and variables to you metric.

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 project element sidebar to the appropriate directory. From there, you can select the project element should be inserted into the syntax.

Click an ellipsis to open the Project Element Sidebar where you can select the element to insert the MAQL 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.

MAQL always works in the context of data available in a given logical data model which determines the available data and utilization of MAQL statements.

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, your MAQL statement 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 #.

  • No labels