Null Handling Example in my 'Extending Module – Creating Custom Calculations' video course

I received feedback on the 'Extending Module – Creating Custom Calculations' video from my '
First Guide to Dashboards using IBM Cognos Analytics (V11)' video course, that I was too fast in this video and hence some viewers are not able to follow what I am trying to teach in the NULL handling (IF .. ELSE) part. 

Hence I am trying to elaborate on this topic in this blog.

In this video, we are learning how to create simple mathematical calulcation items using our own (customized) calculation expression. 

We are start with simple subtraction of two items namely: Potential Sales and Actual Sales. We are calling it Lost Opportunity. 

The expression for Lost Opportunity is: Potential Sales minus Sales Qty.

This would work fine when we are values for both the data items. However, there could be certain months, for certain products, where we have 'Potential Sales' data but the Actual Sales Quantity is not present (NULL) and vice versa.

It is the standard behaviour of any database / compute system that if one of the operands is NULL, then the answer is null. Hence in this kind of scenarios, we will not get the correct value for 'Lost Opportunity', rather we will just get NULL as the answer.

For example: In January, if Product X did not sell, so Sales Qty is NULL but the Potential was to sell 100, then instead of getting 100 minus 0 = 100, we will get NULL in the Lost Opportunity value.

To solve this problem, we have added an IF..ELSE condition. It checks if Sales Qty is null, then returns the Lost Opportunity = Potential Sales value. It also checks if Potential Sales is NULL, then returns Lost Opportunity as (-1 * Actual Sales) because we have actually over-sold the product where we didn't expect any opportunity. 

Note that there are many ways to handle the Null issue. The most common is the use of NVL() function which replaces Null with 0 or any other predefined value. 

However, the purpose of this video if to show that the developer can write their own logic and make use of many constructs in the expression.

I hope this explanation will be useful to the learners alongside the video.