0 Replies Latest reply: Jan 15, 2018 4:21 AM by Bartlomiej Gulej RSS

    Missing value vs null value

    Bartlomiej Gulej



      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?