How can I make null equal to zero in an expression?

[Anonymous]

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?

[mdmukramali]

Dear Chris,

Can you provide some sample data so that we can help you i better way.

and I think your formula should be like

Sum ([Inventory Purchased.Quantity Purchased]) -Sum([Inventory Sold.Quantity Sold])

Thanks,

Mukram

[Anonymous]

Hi,

try in script:

   Load

               if(isnull(Field),0,Field) as Field,            

     From xyz;

Regards

Neetha

[Anonymous · Accepted solution]

I think I answered my own question.

I changed my expression to the following:

  Sum (Alt([Inventory Purchased.Quantity Purchased],0) - Alt([Inventory Sold.Quantity Sold],0))

It seems to have done the trick!

Thanks for those who replied!

[#5]

try this expression in back end

if(IsNull( [Inventory Sold.Quantity Sold])=-1,0, [Inventory Sold.Quantity Sold]) as [Inventory Sold.Quantity Sold]

hope this helps you.

[rajeshvaswani77]

Hi Chris,

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.

All nulls will be treated as zero.

thanks,

Rajesh Vaswani

[Anonymous]

So being new to QlikView, is there a correct way or a best practices way of handling this?  I see many solutions to this question!

[Anonymous]

Hi,

Try all solutions and compare your self the outputs.

decide the good one.

better solution is handling any transformations in Script.