Cannot handle Null cells in calculated columns from layout editor in BIP

Question

We are creating a report using two data set - one is SQL based and another is loaded from excel. Using layout editor we are trying to create a calculated column subtracting  where col2 is from excel data set and col1 from sql data set, where in a cell in col2 the value is null and there is corresponding value in col1  but in the calculated col 3 the value is NULL this is wrong example below

col1    - col2  = col3

100 - NULL = NULL. This is wrong it should be 100.

150 - 50 = 100 - this is coming correctly

Bhaskar Konar · Answer

https://community.oracle.com/products/oracleanalytics/discussion/comment/63645#Comment_63645

Hi,

In that case, please handle the NULL conversion in the Data Model it self.

Jepson Erattuparmbil · Answer

I cannot use functions when I am defining calculated field from a layout editor, refer to attached doc

Null.png

Bhaskar Konar · Answer

https://community.oracle.com/products/oracleanalytics/discussion/24540/cannot-handle-null-cells-in-calculated-columns-from-layout-editor-in-bip

Can you please try replacing NULL with 0 in the calculation. something like:

COL3 = (IFNULL(COL1, 0) - IFNULL(COL2, 0))

Hope this help.

Cheers,