Supressing NULL, BLANK or non-numeric field values in sum

[#1]

I have an excel file which I use to load data into QlikView. One of its columns, let's say 'Hours', can contain empty cells. I load this excel file into a table through scripting. Later I want to calculate the sum of all the cells of the column 'Hours' that complain a condition (Order Type is pending) but taken into account there are some cells that can contain empty fields (I suppose QlikView represent them as '-' hyphen, right?) so in this case I want to treat them as 0 or even ignore them. Hence that I am trying to use sumrange function as below but it is not working:

=num(sumrange({$<[Order Type] = {'Pending'}>}[Hours]),'#.###,##')

[sunny_talwar]

Try this:

=Num(Sum({$<[Order Type] = {'Pending'}>}[Hours]),'#.###,##')

Sum should exclude -, null or blanks

[#3]

I was using following expression as I didn't know that sum function was already excluding, - (hyphen), null or blanks. Ok, good to know that sum function already does it.

=num(sum({$<[Order Type] = {'Pending'}>}if(IsNum([Breakdown duration]),[Breakdown duration])),'#.###,##')

By curiosity, what's the difference between sum and sumrange then?

sumrange treats as 0 all non-numeric values but sum excludes them, but at the end, the result is the same. Am I wrong?

[boorgura]

I believe you are trying to use the "RangeSum" function. I dont think there is any SumRange function in Qlik.

[#5]

Yes, rangeSum sorry.

[sunny_talwar]

Look at the attached example:

You will find out that Sum() is doing the right calculation when I have to Sum a particular field. Where RangeSum() is working when I have to combine ValA and ValB into one expression. I can alternate

RangeSum(Sum(ValA), Sum(ValB)) with Sum(ValA) + Sum(ValB) which will work because Sum converts Null into 0. But if we were doing ValA + ValB and ValA was null, we would see null as the output, where as RangeSum(ValA, ValB) will ignore the null and show the value.

I hope this will clarify the issue?