Using COALESCE function (handling null values)

Sometimes we want to screen companies based on a ratio that might be null. In such cases, we can provide a fallback value using COALESCE function.

Using COALESCE function

A few examples of coalesce function are:

COALESCE( Average return on capital employed 10Years, Average return on capital employed 5Years ) > 20%

The above means that use the return on capital employed of 10years, but in case the ratio is not available then use the 5 years ratio.

Another example might be to provide an absolute value:

COALESCE( Price to Earning , 35 ) < Sales growth 5years median 

Here we are searching for companies with a Price to Earning less than their 5years median PE. However, in case the current PE is null (because of losses), we are taking the fallback values as 35.

Using COALESCE to search for null values

We can also use the coalesce function to search for cases where the value is null.

coalesce( Return over 3years  , 1 ) = 1

Here, we are providing the fallback value of 3 Years average return if the value is not available. And then specifically searching for cases where the value is 1. This will only show the results where 3 years price data is not available for companies. These are probably the companies that were recently listed.