topic Re: Supressing NULL, BLANK or non-numeric field values in sum in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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]),'#.###,##')

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

sunny_talwar — Tue, 26 Jul 2016 12:18:11 GMT

Try this:

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

Sum should exclude -, null or blanks

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

Tue, 26 Jul 2016 13:17:49 GMT

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?

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

boorgura — Tue, 26 Jul 2016 13:33:33 GMT

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

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

Tue, 26 Jul 2016 14:17:43 GMT

Yes, rangeSum sorry.

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

sunny_talwar — Tue, 26 Jul 2016 15:35:54 GMT

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?