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

[eva_filep]

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,

[Anonymous]

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

[Anonymous]

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:
LOAD MonthName(YearMonth) as YearMonth;
LOAD * INLINE [
YearMonth
01.01.2015
01.02.2015
01.03.2015
01.04.2015
01.05.2015
01.06.2015
01.07.2015
];

TABLE2:
LOAD MonthName(YearMonth) as YearMonth,Value;
LOAD * INLINE [
YearMonth, Value
05.01.2015, 15
03.04.2015, 25
01.06.2015, 35
];

[Peter_Cammaert]

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

[Anonymous]

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