14 Replies Latest reply: Apr 25, 2017 1:21 PM by Robin Hausdörfer

# Distinguishing missing values from null values in if clauses

Hi folks,

I have the following scenario:

DimensionYear1Year2Year3
Company 11,000.001,000.00(division by zero result)
Company 22,000.001,000.00Missing value
Company 35,000.003,000.00Missing Value

What I want is to replace null values by 'no Revenue', but nothing has to happen with missing values columns.

I've tried:

Alt(

sum(profit) / sum(revenue), 'no Revenue')

Also:

Alt(

If(IsNull(sum(revenue) = 0,

sum(profit) / sum(revenue), 0), 'no Revenue')

However, the formula above also replaces missing values by 'no Revenue'.

Could you help me with this?

Thanks,

Rubens Rodrigues

• ###### Re: Distinguishing missing values from null values in if clauses

In QlikView charts, you can substitute both Missing and Null values with two separate symbols - wouldn't that help? The settings are at the bottom of the Presentation tab.

Cheers,

Oleg Troyansky

• ###### Re: Distinguishing missing values from null values in if clauses

It doesn't fit in my case, because what I'm really trying to do is to treat division by zero.

As far as I understood, Qlikview is considering the division by zero result as "missing value".

I want something similar to the Excel function IFERROR.

I've tried to force null values in cells which division by zero results were expected and it woked. However, when I use the Alt function, it replaces both null and missing values by whatever I set.

Very confuse.

Thanks

• ###### Re: Distinguishing missing values from null values in if clauses

In my testing, division by zero is treated as NULL, not as MISSING.

The Alt() function returns the first non-NULL numeric value.

• ###### Re: Distinguishing missing values from null values in if clauses

If I just enter with the divivion by zero formula and go to the chart properties and substitute the value for Null, nothing happens. Howver, it works if I substitute the value for missing. This is the reason I thought Qlikview considered the division by zero results as "missing values".

• ###### Re: Distinguishing missing values from null values in if clauses

I tried to generate NULL and MISSING values, including division by zero, and everything appears like NULL - even when the value is actually missing.

• ###### Re: Distinguishing missing values from null values in if clauses

As an aside: Suppress Missing isn't working because "this option is not functional on Straight tables" (QV Desktop Help). Which may mean that Missing values are altogether alien to straight tables.

Weird logic...

• ###### Re: Distinguishing missing values from null values in if clauses

I think that is not entirely true. This option is functional on straight tables. But I don't understand at all why the last row will be displayed.

(QV11.2 SR15)

TABLE1:
YearMonth
01.01.2015
01.02.2015
01.03.2015
01.04.2015
01.05.2015
01.06.2015
01.07.2015
]
;

TABLE2:
YearMonth, Value
05.01.2015, 15
03.04.2015, 25
01.06.2015, 35
]
;

• ###### Re: Distinguishing missing values from null values in if clauses

Indeed, you get a change in your output table. But is this really related to a "Missing" value. Seems more like activating the "Show All Values" setting in the Dimensions tab.

And in which field did you change the presentation value? Null or Missing? This looks exactly like what Oleg already said.

I'm beginning to think that QlikView considers missing in a Straight table as undecidable. How should a QlikView straight table know that something should be there when there is no data? (Almost) every aggregation function will return something, at least a 0 value which is fundamentally different from Null or missing. And NULL already is equivalent to No Value, No Data, "Nothing to see here"...

Good subject for elaborate discussion

• ###### Re: Distinguishing missing values from null values in if clauses

I haven't activated "show all values" for the dimension as you can see in my attachment...

• ###### Re: Distinguishing missing values from null values in if clauses

have you tried:

If(IsNull(sum(revenue)) = 0,sum(profit) / sum(revenue),if(IsNull(sum(revenue)) = -1, 'no Revenue', sum(profit) / sum(revenue)))

• ###### Re: Distinguishing missing values from null values in if clauses

Hi guys!

Still not working!

I I screenshotted my graphic to try explain better:

• IsNull(sum(revenue))

• If(IsNull(sum(revenue)) = 0, 'NULL')
• I was expecting only the highlighted informatin above to be replaced by NULL, but it's not what happen:

Thanks guys.

Rubens

• ###### Re: Distinguishing missing values from null values in if clauses

Here I1m just testing NULL and MISSING. It's not the real logic.

• ###### Re: Distinguishing missing values from null values in if clauses

I'm just spit balling here, but I think if you include a comparison to a field that is always on your revenue table you can do it.  For instance, a row number field.  Then you could do something like:

=if(isnull(RevenueRowNoField),'MissingValue',if(sum(Value)=0,'No Revenue',sum(Value)))

• ###### Re: Distinguishing missing values from null values in if clauses

I am working in Qlik Sense not Qlik View, but I guess the logic must be the same.

Your field that stores the following values:

sum(profit) / sum(revenue)

Missing

no Profit

has to be a dimension not a measure since you have text mixed up with numbers.

Therefore sum function cannot be used in your formula.

I got this:

by using the formula:

If(Revenue= 0,'no Revenue',

if(IsNull(Revenue),'Missing', Num( Profit/Revenue, '%#,##0.#', '.' , ',' )))

Maybe you'd need to create Revenue and Profit master items to sum up your original fields(?) and then you can create the dimension

Regards,