Missing value - Does it impact calculations?

[bonniehsueh]

I'm trying to subtract two sets.  (Source: OLAP)

[Product Revenue] - [Cost of Goods Sold]
[Product Revenue] = 5000
[Cost of Goods Sold] = missing a value

I would think a missing value defaults to Zero, so when I take the difference, I expect to see 5000 (5000-0 = 5000) instead, I'm getting a missing value.

Has anyone seen a missing character impact a calculation?

[Gopinath]

Missing value defaults to Null so, try calculating it as

[Product Revenue] - if([Cost of Goods Sold] Is Null) then (0) else ([Cost of Goods Sold])

[bonniehsueh]

Yes, I hardcoded a number using a similar if statement as you did to force a value to be calculated and numbers showed up.

Thanks for your thoughts gobinath!

[blom0344]

No need for an If construct. The Coalesce function is specifically aimed at doing what you need!