Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

14 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor II
Contributor II

have you tried:

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

Anonymous
Not applicable
Author

Hi guys!

Still not working!

I I screenshotted my graphic to try explain better:

  • IsNull(sum(revenue))

Image1.png

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

Image2.png

Thanks guys.

Rubens

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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
Not applicable
Author

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