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.
Note that the following terms are used interchangeably throughout the MAQL section.
|Responsive UI||Pixel Perfect UI|
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:
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
- Attribute Values
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).
- On the Manage tab, navigate through the following path - Data Tab > Metrics > click Create Metric:
The Metric Editor appears.
- Click Custom metric.
Custom Metric Editor appears.
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 your 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.
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, 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
# 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 #.
Learn MAQL at GoodData University
GoodData university offers a comprehensive MAQL introductory course that will guide you through building metrics in several real-life scenarios.