by marcus.wong@keboola.com

From MAQL Reference Guide:

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

Syntax:

SELECT IFNULL(…,…)

SELECT IFNULL(metric, replacement_value)

Examples:

SELECT IFNULL(SUM(Amount), 0)

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

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

Sometimes IFNULL won’t work when there is no row with the particular attribute that you want to be aggregating on.  Example:

Use Case:

Deutsche Telekom wants a graph that shows the cumulative total tenants by month.  However, for January 2014, they stopped adding tenants completely, but still want to know how many cumulative tenants there were in january.  If you tried the normal RUNSUM function, during the month of january there would be no bar (or data point) for the month.  in february, it would skip from december to february.  

This is how we do it:

SELECT IFNULL(SELECT RUNSUM(metric for counting new tenants),0)*1

What the “*1” does is it multiples the 0 value, by 1, which gives us a 0 value for january.