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:
Examples:
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:
What the “*1” does is it multiples the 0 value, by 1, which gives us a 0 value for january. |