Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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)))
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
];
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
I haven't activated "show all values" for the dimension as you can see in my attachment...