Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please see below extract.
As can be seen from above, the PRQTY and Calculation (QTY+PRQTY) is text, and the [Sum of QTY] and QTY fields are numeric based on the text alignment.
Trouble is, that I am trying to do a calculation QTY+PRQTY, and the result looks as follows:
Seems that when the PRQTY is equal to 0 (isnull value was changed to 0 with the hope of fixing the issue) it does not keep the QTY as the result, instead it says 0.
I believe this is a result of a formatting issue , however i have tried the below formulas:
I have also tried using the "Number" sheet and changed the format to number there, and to Fixed to etc. with no result.
I've resorted to the below formula, which yields the correct result, i am just unsure as to why the null values, cannot be converted into number.
Can Anyone share some insight?
Regards,
Try with RangeSum
RangeSum(QTY, PRQTY)
could you do a numericcount() and a missingcount() on your fields and post the example please...
Ruan, NULL is not a value, so it's not zero value.
Have a look at
Math operators like + return NULL if one of the operands is NULL.
Rangesum() however, like suggested by Sunny, is converting NULL to zero in its calculations.
The option on presentation tab to replace NULL with any given text, like '0' is just for display purposes, it does not change how Qlik operates on your data.
If Null value is used in any of the arithmetic operator (+,-,/,*) results always in Null. So in your case you need to convert NULL values to 0, so as to perform addition of two number.
Example
With Null - (2+ Null =Null )
Null converted to 0 - ( 2+0=2)
You can use Rangesum() function as suggested by Sunny
Also, you can use Alt() function alt(QTY,0) + alt(PRQTY,0)