How can I make null equal to zero in an expression?
I am relatively new to QlikView and I am making a practice QV document using the Northwind Database. I am trying to write an expression for [Total Inventory]. The expression itself I think is a simple expression.
Sum ([Inventory Purchased.Quantity Purchased] - [Inventory Sold.Quantity Sold])
The problem I am having however is that in the [Inventory Sold.Quantity Sold] They did not use zero's for zero quantity and instead the field is null.
Is there a way to make null equal to zero so the expression will return the desired calculation?
Instead of Sum you can use Rangesum. From help below is the text that explains rangesum.
rangesum(expr1 [ , expr2, ... exprN ]) Returns the sum of a range of 1 to N arguments. As opposed to the + operator, rangesum will treat all non-numeric values as 0. The argument expressions of this function may contain Chart Inter Record Functions with a third optional parameter, which in themselves return a range of values. Examples: rangesum (1,2,4) returns 7 rangesum (1,'xyz') returns 1 rangesum (null( )) returns 0 rangesum (above(count(x),-1,3)) returns the sum of the three results of the count(x) function evaluated on the row below the current row, the current row and the row above the current row.
