Distinguishing missing values from null values in if clauses

[Anonymous]

Hi folks,

I have the following scenario:

Dimension	Year1	Year2	Year3
Company 1	1,000.00	1,000.00	(division by zero result)
Company 2	2,000.00	1,000.00	Missing value
Company 3	5,000.00	3,000.00	Missing 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

[Oleg_Troyansky]

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

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

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

[Oleg_Troyansky]

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

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

[Anonymous]

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".

[Oleg_Troyansky]

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

[jpitfield]

have you tried:

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

[Anonymous]

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

[Peter_Cammaert]

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...

[Anonymous]

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