Text to Number not working

[Anonymous]

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:

• Num(QTY) as QTY
• Num#(QTY) as QTY
• (Num (Trim([QTY]),'#,##0', '.' , ',')

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,

[sunny_talwar]

Try with RangeSum

RangeSum(QTY, PRQTY)

[Anonymous]

could you do a numericcount() and a missingcount() on your fields and post the example please...

[swuehl]

Ruan, NULL is not a value, so it's not zero value.

Have a look at

NULL handling in QlikView

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.

[Kushal_Chawda]

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)