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?
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])
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!
No need for an If construct. The Coalesce function is specifically aimed at doing what you need!