topic Re: SUM of null in script in QlikView

SUM of null in script

qlikplaut — Mon, 24 Feb 2014 10:39:57 GMT

Hello everyone,

I have a problem.

I have a field with normal values and null values. When perform a group by in the script with sum(field), the result returns 0 even if the values are null. I even tried placing a 'N' when null, but i get the same result sum(field)=0 for these values. I want the sum of null values to be a null value as well.

Basically I have this:

for values=null or 'N', sum(field)=0

I need this:

for values=null or 'N', sum(field)=Null or 'N' and not 0

Thank you,

Bogdan

Re: SUM of null in script

alexandros17 — Mon, 24 Feb 2014 10:52:03 GMT

you cannot unfortunately, because sum gives always 0 even with null values.

Re: SUM of null in script

hic — Mon, 24 Feb 2014 15:21:59 GMT

Well, Sum(field) is 0 if field is NULL... So QlikView does the right thing.

If your Group By in the script looks like the following:

Load

Dimension,

Sum(Number) as NumberSum

From ... Group By Dimension ;

Then you might get NumberSum=0 for some values of "Dimension". If you want to omit these records, you should simply add a Where clause, e.g.:

Load

Dimension,

Sum(Number) as NumberSum

From ... Where IsNum(Number)

Group By Dimension ;

HIC

Re: SUM of null in script

JuMo — Mon, 15 Sep 2025 10:28:08 GMT

I have a similar case:

Load
Dimension,
Sum(Number1) as NumberSum1
Sum(Number2) as NumberSum2
Sum(Number3) as NumberSum3
From ... Group By Dimension ;

But in my case any of the fields NumberX can be empty when the others are not
In that case I cannot add Where clause

What else could I do ?