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

    Missing value vs null value

    Bartlomiej Gulej

      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):

       

      nulls.png

       

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