I have the following scenario:
|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.
sum(profit) / sum(revenue), 'no Revenue')
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?
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.
Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense
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.
In my testing, division by zero is treated as NULL, not as MISSING.
The Alt() function returns the first non-NULL numeric value.
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".
I tried to generate NULL and MISSING values, including division by zero, and everything appears like NULL - even when the value is actually missing.
have you tried:
If(IsNull(sum(revenue)) = 0,sum(profit) / sum(revenue),if(IsNull(sum(revenue)) = -1, 'no Revenue', sum(profit) / sum(revenue)))
Still not working!
I I screenshotted my graphic to try explain better:
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.