Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

bartlomiej_gule
New Contributor

Missing value vs null value

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?

Tags (2)
Community Browser