Missing value vs null value

BeeGees — Mon, 15 Jan 2018 09:21:16 GMT

Hi,

I'm calculating percentage margin. The formula is as simple as:

margin (percentage) = margin (value) / sales (value)

But when sale (value) <= 0 or sale cost = 0 the margin (percentage) makes no sense (it gives huge negative numbers, which disturbs analysis). So I want to get rid of that. I did that by following:

margin (percentage) = if( sales (value) <= 0 or sale cost = 0, null, margin (value) / sales (value))

I almost works. Almost because that null value i put, doesn't count as missing value somehow (therefore when i uncheck "Include null values" in any dimension on graph/chart - they are included nonetheless).

2 dimensional heatmap shows that perfectly (black with "-" are my NULLs, while blank, white squares are actual missing values (no sales within current month for given dimension, which is product manager in that case):

Any ideas how to make my NULLs act like actual missing values?

topic Missing value vs null value in App Development

Missing value vs null value