In the GoodData Portal, advanced metric wizards and custom metric editor are useful whenever you'd like to create metrics that are more complex than mere aggregations of a given fact. For example, you may want to use MAQL statements to write metrics with built-in filters, mathematical operations, or rules that dictate how the metric is affected by report attributes or filters.
After a metric has been added to the global metrics, you can only edit it through the Manage page.
How to Access the Advanced Metric Editors
You can access the Advanced Metric Editors in several ways:
- Manage Page > Data Tab > Metrics:
The Create Metric button is in the upper right corner of the Metrics Page.
- Reports Page > What Pane
- The (advanced) link next to the Add New Metric button in the What pane
- The Advanced Metric Editor link that appears in the right-most column when the Simple Metrics Editor has been opened (by clicking the Add New Metric button)
Create a Metric Using a Metric Wizard
The GoodData Portal offers three Metric Wizards in addition to its Custom Metric Editor.
Metric Wizards allow you to quickly create metrics that involve performing an operation on a preexisting metric. Each wizard can be accessed from the Advanced Metric Editor dialog:
Percentage Share Metric Wizard
The Percentage Share Metric Wizard allows you to create a new metric that uses a percentage to show the relationship of some part to a whole.
A share metric requires the following definitions:
- A metric (such as Sales) to serve as the "part"
- An attribute (such as Year) by which to aggregate this metric, to serve as the "whole"
Difference Metric Wizard
Use the Difference Metric Wizard to create a new metric that calculates the difference between an aggregated value and a fixed number. For example, you can create a metric that calculates the difference between year-to-date sales and a metric that represents the sales quota.
A difference metric requires the following definitions:
- A metric (such as Year-to-date Sales) to serve as the value that will be subtracted
- An attribute by which this metric will be aggregated (such as 2005) to serve as the fixed value that the above metric will be subtracted from
Ratio Metric Wizard
You can relate two metrics to one another with the Ratio Metric Wizard. For example, you can create a metric that calculates a Revenue to Expense ratio.
A ratio metric requires the following definitions:
- The first metric to be used in your ratio (for example, Revenues)
- The second metric to be used in your ratio (for example, Expenses)
Custom Metric Editor
The Custom Metric Editor provides an interface that serves as a guide for creating your own metrics in MAQL, a powerful data querying language.
Access the Custom Metric Editor by opening the Metric Editor Dialog (see above) and clicking Custom metric beneath the three Metric Wizard options.
From the Custom Metric Editor you can define a metric by entering MAQL statements directly into a text field.
Project elements in your MAQL definitions are automatically color-coded so you can keep track of which elements you've included in your metric and ensure your MAQL syntax is valid:
MAQL syntax must be valid in order for a custom metric to be saved. If you try to save a metric with invalid MAQL syntax, an alert pops up that will help you pinpoint the issue.
In the following example, the SELECT keyword is missing from the start of the metric's definition. All MAQL definitions begin with "SELECT."
Project Element Sidebar
The sidebar to the right provides an organized directory of all the project's elements (facts, metrics, attributes, attribute values, and variables)—all in the form of snippets that can be inserted directly into your metric's definition.
While MAQL syntax can typically be typed directly into the Metric Editor text field, project elements like facts, metrics, attributes, attribute values, and variables must be added to metric definitions using the Project Element Sidebar.
Click an element's heading to view its contents.
When you find an element of interest, double-click it, or select it and click Add Selected, to insert it into your metric's MAQL definition in the text field.
Tabs toward the bottom of the dialog contain additional MAQL operators, functions, and keywords that can be used in your metric definitions. In the tab directories, click the linked name of a MAQL operator, function, or keyword to insert the correctly structured MAQL syntax into the Metric Editor. These MAQL operators, functions, and keywords combine with the project elements (found in the sidebar, detailed above) to create meaningful MAQL metric definitions. For more information, see MAQL Expression Reference .
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.
The Metric Editor supports copy and paste, which you can use to manipulate syntax within a metric's MAQL text field, or to duplicate/move syntax from one metric to another.
Examples of MAQL Syntax
Consider the following examples of MAQL syntax:
Return the largest Sale_Amount fact value
Compute the sum of all sales
Multiply a predefined metric by some value
SELECT Profit * 0.03
Divide one metric by another metric
SELECT New_Revenue / Revenue_Last_Year
Return the Payment value for yesterday
SELECT Payment WHERE Date = THIS – 1
Return the Revenue value for the fifth month of 2006
SELECT Revenue WHERE Year=2006 AND Month=5
Compute the average number of overtime hours that were worked in California in 2007
SELECT AVG(Overtime Hours) WHERE Store_State IN (CA) AND Year IN (2007)
Return Revenue values for all years except for 2006
SELECT Revenue WHERE Year <> 2006
VIDEO - Advanced Metric Editor Walkthrough