Method to treat Null values as zero

This is only one example of where this problem may occur:

We have a complex FM model which has been designed to return relative time values based upon the notes discussed here - https://www.ibm.com/developerworks/data/library/cognos/page90.html

The issues occur when there are no values returned for a particular period and the report/dashboard author wishes to calculate a variance or total e.g. Jan sales + Feb sales. If either of the values is null then the result will be null.

This issue cannot be easily dealt with in FM but must be catered for in the report or dashboard using the Coalesce statement. This concept is probably well known to advanced users of the products but would be beyond the capabilities of novice or self serve users of the product, especially when those users only make use of the Calculate icon on the tool bar rather than create calculations from scratch using data items.

If the calculated field is subsequently aggregated in to a total row, the problem is further exacerbated as users may not be aware of the null results having an impact on the total, thus leading to invalid data being regarded as valid.

This issue may also occur with data modules and file uploads, cases where outer joins may return null values, or when using conformed dimensions to return multiple measures eg sales vs returns. Similar issues will also occur if concatenating string values.

Null processing of this type is generally poorly handled by most database and BI applications so a solution to this would be a major selling point for CA