# XAE Product Introduction

The GoodData platform combines the power of highly customizable and understandable dashboards based on our powerful multi-dimensional analytical query language.

**Contents:**

## GoodData Extensible Analytics Engine

The Extensible Analytics Engine leverages our proprietary MAQL/XAE Language, which offers context-dependent metric dimensionalities, allowing users to apply metrics in many contexts without changing the metric’s definition. You can even nest one metric in another to build more complex KPIs.

The advantage of the GoodData platform becomes apparent when you begin manipulating data without impacting the underlying physical data model. GoodData logical data models abstract from the complexities of a physical data model, allowing users to focus on business use cases, rather than how a project’s data is stored.

With the new architecture and all-new features, XAE provides more reporting value, tackling more complex use cases and improving existing ones. In its most advanced form including better query optimization and execution and more.

A number of XAEs new offerings are owed to input from our business users. Exception reporting (comfortable *NULL* handling), advanced ranking (including percentage ranks, ranking within group, etc.), and complex conditional reporting (IF-ELSE and CASE) are all enabled by the new XAE. We’re also introducing new running total aggregation functions that return cumulative aggregations of a product fact and can be introduced in tables, charts, or nested within other metrics.

The XAE is designed to help streamline the way organizations analyze and make data driven decisions on a daily basis.

## Absolute Value, Signum and Square Root

### ABS( )

**Description**

Returns the absolute value of a number. Negative values are returned positive. Positive values are unchanged.

**Syntax**

`SELECT ABS(number)`

`SELECT ABS(metric)`

**Examples **

`SELECT ABS(-5)`

…returns the value “5”

`SELECT ABS(10)`

…returns the value “10”

`SELECT ABS((Total_Amount)-(SELECT Total_Amount WITHOUT PF))`

### SIGN( )

**Description**

The signum function returns the sign of a number. If the number is positive, sign returns 1. If the number is negative, sign returns -1. If the number is zero, sign returns 0.

**Syntax**

`SELECT SIGN(number)`

`SELECT SIGN(metric)`

**Examples **

`SELECT SIGN(-3.87)`

…returns the value -1

`SELECT SIGN(33)`

…returns the value 1

`SELECT SIGN(0)`

…returns the value 0

### SQRT( )

**Description**

The square root function returns the square root of a number or metric input. Negative or complex inputs result in a *NULL* output.

**Syntax**

`SELECT SQRT(number)`

`SELECT SQRT(metric)`

**Examples **

`SELECT SQRT(25)`

…returns the value “5”

`SELECT SQRT(33.6)`

…returns the value “5.796550698”

`SELECT SQRT(SELECT SUM(Sales))`

…returns the sqrt of the Total Sales metric

## Running Totals

Previously, running totals could be added to GoodData tables using the secondary-click contextual menu. This created a new column to the right of the original that automatically totaled the selected column’s cells. However, the actual metric used to compute the column’s values was inaccessible.

Now you can use the RUNSUM, RUNAVG, RUNMIN, and RUNMAX functions in MAQL to create running total metrics that can be reused in other metrics – where they can be manipulated with other MAQL functions – or added to any number of other reports.

### RUNSUM / RUNAVG / RUNMIN / RUNMAX

**Description**

Takes the running total of a project fact. In a table broken down by a date attribute, the running total value on any given day would be calculated by aggregating values from all prior days along with that of the current day.

**Syntax**

`RUNSUM(fact)`

`RUNAVG(fact)`

`RUNMIN(fact)`

`RUNMAX(fact)`

`RUNSUM(metric)`

`RUNAVG(metric)`

`RUNMIN(metric)`

`RUNMAX(metric)`

**Examples SELECT RUNSUM(Sales) **

`SELECT RUNAVG(Leads) WHERE Year={`

`this`

`}`

`SELECT RUNMIN(Probability) WHERE Amount > 500000`

`SELECT RUNMAX(Opportunities) BY Quarter/Year`

`SELECT RUNSUM(Won Opportunities) WITHOUT PARENT FILTER`

The current limitation is that the dimensionality can be only (and precisely) one date/time attribute.

## Conditional Statements

### IF THEN ELSE

**Description**

*IF THEN ELSE* statements return one of two possible values, or perform one of two possible computations, depending on whether some condition is met. The condition that needs to be met follows the keyword *IF* and can be constructed using any of the filtering keywords or relational operators (IN, NOT IN, BETWEEN, NOT BETWEEN, =, < , <=, >, >=, <>).

The first possible outcome follows the keyword THEN, and the second possible outcome follows the keyword ELSE. The outcomes that are returned can be numerical values or arithmetic operations. *All IF THEN ELSE* statements conclude with the *END* keyword.

If there are three or more conditions (in addition to an *else* state), use the *CASE* statement described below.

**Syntax**

`SELECT IF … THEN … ELSE … END`

`SELECT IF condition THEN number ELSE number END`

`SELECT IF condition THEN arithmetic_operation ELSE arithmetic_operation END`

**Examples SELECT IF SUM(Amount) >= AVG(Amount) THEN 10 ELSE 0 END SELECT IF SUM(Duration) - AVG(Duration) > 2000 THEN 0 ELSE 1 END SELECT IF AVG(Probability) > 0.5 THEN SUM(Amount) * 10 ELSE SUM(Amount) / 10 END **

### CASE

**Description**

*CASE* is used for complex conditional statements that contain three or more conditions. Following the *CASE* keyword, conditions and outcomes follow the *WHEN* and *THEN*, respectively. If none of the *WHEN* conditions are met, the outcome following *ELSE* is returned. Outcomes can be numerical values or arithmetic operations. All *CASE* statements conclude with the *END* keyword.

**Syntax**

`SELECT CASE WHEN … THEN …, WHEN… THEN… ELSE… END`

`SELECT CASE WHEN condition1 THEN outcome1, WHEN condition2 THEN outcome2 ELSE outcome3 END`

**Examples**

```
SELECT CASE WHEN ACTIVITY_TYPE IN(Email,Web_Meeting) THEN 1,
WHEN ACTIVITY_TYPE IN(Phone_Call,In_Person_Meeting) THEN 2
ELSE 0 END
```

```
SELECT CASE WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) - SUM(Lost) > 100000 THEN 2,
WHEN SUM(Amount) > SUM(Lost) AND SUM(Amount) - SUM(Lost) < 100000 THEN 1
ELSE 0 END
```

### IFNULL

**Description**

*IFNULL* allows you to predefine the way GoodData deals with any missing values that are returned by a metric expression (metric, fact aggregation, or arithmetic operation). If a metric expression that is wrapped within an *IFNULL* statement ever returns a null value, the replacement number specified in the second parameter of the *IFNULL* function will be inserted in place of null.*IFNULL* is especially useful in cases where a sub metric is referred to by another metric’s definition. Wrapping the sub metric in an *IFNULL* statement keeps the encompassing metric from becoming null due to one of its components returning a null value.

If you use a metric as a replacement value, this report will be not computable due to an improper metric definition.

**Syntax**

`SELECT IFNULL(…,…)`

`SELECT IFNULL(metric_expression,replacement_value)`

**Examples**

SELECT IFNULL(SUM(Amount), 0)

SELECT IFNULL(SUM(FB_Cost + TW_Cost), 0)

SELECT IFNULL(SUM(Amount) + 100, 1)

SELECT IFNULL((SELECT SUM(Payments)

WHERE Product IN (Explorer, Educational)), 0)

## Ranking Functions

XAE now supports the *RANK* function in addition to basic TOP(n) and BOTTOM(n) ranking (e.g. TOP(5)sales reps).

In combination with various formulations of *WITHIN* statements, *RANK* allows you to rank values within sub-groups associated with a report’s attribute values. This would be useful for ranking the top 5 sales reps within each region, in a report containing sales rep data across all sales regions.

When accompanied by various formulations of *WITHIN* statements, *RANK* can be used to carry out the following:

- dense rankings
- ranking by percentage
- cumulative distribution

When *RANK* is used without an accompanying *WITHIN* statement (e.g. SELECT RANK(Avg. Won, the function sequentially ranks all metric values across the rows in a report, resulting in an identical output as `RANK(...) WITHIN(ALL IN ALL OTHER DIMENSIONS)`

. When a *WITHIN* statement is appended to a *RANK* function, it can be used with either of two parameter formulations, resulting in different outputs. These are explored in the table below.

WITHIN | Output |
---|---|

WITHIN ( attr, …, ALL attr, … ALL OTHER EXCEPT … ) | Ranks within sub-groups that are specified by the attributes contained in the WITHIN statement’s parameter. Ranks within clusters of current dimensionality altered using specified by rules, i.e. ranking within group. Respecting the report dimensionality specified in HOW. Any statement which can be in BY clause may be used here. |

WITHIN (CURRENT) | Ranks within current dimensionality ignores the report dimensionality from HOW. Every row of the report is handled as a separate group and so the ranking metric will provide only one value, here 1, as a results at all rows. Hardly useful with TOP/BOTTOM ranking. |

The full syntax of the RANK function is described in following text.

### RANK

**Description**

The ranking function returns a numerical rank for each of a report’s metric values in ascending [ASC] order (smallest value ranked first) or descending [DESC] order (largest value ranked first). Ascending order is the default, for cases where an order is not explicitly specified.

A key feature of the *RANK* function is the *WITHIN* keyword, which allows you to carry out rankings of multiple attribute sub-groups within a single report.

**Syntax** ```
```

SELECT RANK(…) [ASC|DESC] [WITHIN(…)]

`SELECT RANK(metric)`

`SELECT RANK(metric) WITHIN( CURRENT )`

`SELECT RANK(metric) WITHIN( Attribute_1, Attribute_2, [...] )`

`SELECT RANK(metric)WITHIN( any BY statement )`

**Examples SELECT RANK(Avg. Won)
SELECT RANK(Amount) ASC WITHIN(Year(Closed))
SELECT RANK(Amount[SUM])WITHIN(CURRENT)
SELECT RANK(Amount[SUM])WITHIN(ALL OTHER)
**

As with *RANK*, *TOP* and *BOTTOM* ranking functions can be used without a *WITHIN* statement for ranking that disregards sub-groups within a report. Appending formulations of *WITHIN* to *TOP* and *BOTTOM* allows for rankings that include only the top or bottom n values within each sub-group in the final report output.

### WHERE TOP|BOTTOM (n) IN

**Description**

This *TOP* and *BOTTOM* ranks are a specific version of ranking functions. It will rank and filter you the result of a specific metric based on *TOP* or *BOTTOM* key words. Its syntax requires a *SELECT* keyword preceding the metric that follows *IN*. It interprets the ranking metric as a sub-report.

Now supports percent parameters in place of n.

**Syntax**

`SELECT … WHERE TOP(…) IN … WITHIN …`

`SELECT metric1 WHERE TOP(n) IN (SELECT Metric2 BY A1, A2...) WITHIN (...)`

**Examples SELECT Amount WHERE TOP(5) IN (SELECT Avg. Won BY Product) **

But we also support the *OF* keyword instead of *IN*. The syntax then becomes easier and also the usability increases. The difference is also how the metrics are interpreted in the backend infrastructure. The *OF* allows ranking by more than one metric and interprets the metric as a submetric, in contrast to IN which requires a *SELECT* and interprets the ranking metric as a subreport.

### WHERE TOP|BOTTOM (n) OF

**Description**

This *TOP* and *BOTTOM* ranks are a specific version of ranking functions. It will rank and filter you the result of a specific metric based on *TOP* or *BOTTOM* key words. Allows ranking of more than one metric and interprets “the metric” as a sub-metric.

**Syntax**

`SELECT … WHERE TOP(…) OF … [WITHIN …]`

`SELECT metric1 WHERE [TOP|BOTTOM](n) OF ( M1, ... )`

`SELECT metric1 WHERE [TOP|BOTTOM](n) OF ( M1, ... ) WITHIN ( ... )`

**Examples SELECT Amount WHERE TOP(5) OF (Avg. Won) SELECT Won WHERE TOP(10) OF (Won) WITHIN(Region) **

`SELECT Amount WHERE BOTTOM(5) OF (Lost) `

` `

`WITHIN(Region, Year (closed))`

### WHERE TOP|BOTTOM (n%) OF

**Description**

The percentage rankings are similar to all other *TOP* and *BOTTOM* but using the % sing in the specification of the ranking TOP|BOTTOM parameter n.

**Syntax**

`SELECT … WHERE [TOP|BOTTOM](n%)`

`SELECT metric1 WHERE [TOP|BOTTOM](n%)`

**Examples SELECT Amount WHERE TOP(5%) OF (Amount) AND Product=Explorer SELECT RANK(Top 5% parent) WHERE TOP(50%) OF (Top 5% parent) **