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(...)

SELECT SQRT(constant)

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 square root of a Total Sales metric

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 the null value.

IFNULL is especially useful in cases where a sub metric is referred to within 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.

Note: Supported replacement values include constants, but not other metric expressions – which would lead 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)

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 defined condition 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.

Note: If there more than two possible conditions to be defined, 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

Description

CASE is used for complex conditional statements that contain three or more conditions. Following the CASE keyword, conditions and outcomes follow the keywords 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, meeting) THEN 1, WHEN activity_type IN(phone_call, 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

WITHIN

Output

WITHIN (attribute1, attribute2, ... | ALL attribute | ALL OTHER EXCEPT attribute)

Ranks metric values within sub-groups that are specified by the attributes contained in the WITHIN statement’s parameter.

WITHIN (CURRENT)

Ranks within current report dimensionality (defined by the report's attributes), where every row of the report is handled as a separate group and thus each value is ranked '1'.

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(attribute1, attribute2, [...])

SELECT RANK(metric) WITHIN(attribute ALL OTHER | ALL OTHER | ALL OTHER EXCEPT FOR)

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)

Description

Unlike the RANK function, WHERE TOP|BOTTOM IN serves as a filter that limits the number of report values returned in the final report output. Its syntax requires a SELECT keyword preceding the metric that follows the IN keyword.

Note: Now supports percent parameters: n%.

Syntax

SELECT … WHERE TOP(…) IN … WITHIN …

SELECT metric1 WHERE TOP(n) IN (SELECT metric2 BY attribute1, attribute2...) WITHIN (...)

Examples

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

Description

Similar to WHERE TOP|BOTTOM IN, WHERE TOP|BOTTOM OF serves as a filter that limits the number of report values returned in the final report output. Using the OF keyword (rather than IN) allows ranking of more than one metric and interprets the ranking metric as a sub-metric.

Note: Now supports percent parameters: n%.

Syntax

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

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

SELECT metric1 WHERE [TOP|BOTTOM](n) OF ( metric2, ... ) 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))

Description

Percentage rankings are similar to TOP|BOTTOM ranking filters but support percent parameters in place of 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)

Description

Takes the running total of a project fact or metric. 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.

 Note: Running total metrics can only be broken down by one date attribute at any given time.

Syntax

RUNSUM(...)  |  RUNAVG(...)  |  RUNMIN(...)  |  RUNMAX(...)

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