topic Re: Handling Null Values in GroupBy Sum Operations in QlikView

Handling Null Values in GroupBy Sum Operations

JuMo — Wed, 17 Sep 2025 13:07:03 GMT

Hello

I have three fields that may sometimes contain null values. When I perform a group by operation in my script with 'Sum(NumberSumX)', the result returns 0 even when the values are null. I want the sum of null values to be null as well.

Here's my current script:

Load

Dimension,

Sum(Number1) as NumberSum1,

Sum(Number2) as NumberSum2,

Sum(Number3) as NumberSum3

From ...

Group By Dimension;

I've seen solutions using a where statement, but they seem to only work with a single field. I'm having trouble adapting it to work with multiple fields. In my case, any of the NumberX fields can be null while the others are not.

How can I modify my script to achieve the desired result?

Re: Handling Null Values in GroupBy Sum Operations

robert_mika — Wed, 17 Sep 2025 13:39:15 GMT

Did you try to add IF statemt that if SUM()=null(), null()...

Re: Handling Null Values in GroupBy Sum Operations

QFabian — Wed, 17 Sep 2025 13:44:02 GMT

Hi @JuMo , what about something like this, doing the sums and then replacing them with null when is 0 :

Data_Aux:

Load

Dimension,

Sum(Number1) as NumberSum1_Aux,

Sum(Number2) as NumberSum2_Aux,

Sum(Number3) as NumberSum3_Aux

From ...

Group By Dimension;

Data:

Load

Dimension,

if(NumberSum1_Aux = 0, null(), NumberSum1_Aux) as NumberSum1,

if(NumberSum2_Aux = 0, null(), NumberSum2_Aux) as NumberSum2,

if(NumberSum3_Aux = 0, null(), NumberSum3_Aux) as NumberSum3

Resident Data_Aux;

drop table Data_Aux;

Re: Handling Null Values in GroupBy Sum Operations

JuMo — Wed, 17 Sep 2025 14:02:22 GMT

My first idea was

If(IsNull(Number1),Null(),Sum(Number1)) as NumberSum1,
If(IsNull(Number2),Null(),Sum(Number2)) as NumberSum2,
If(IsNull(Number3),Null(),Sum(Number3)) as NumberSum3

but i get an "Invalid expression"

But that's not exactly what you are proposing, I have tested your solution

If(IsNull(Sum(Number1)),Null(),Sum(Number1)) as NumberSum1,
If(IsNull(Sum(Number2)),Null(),Sum(Number2)) as NumberSum2,
If(IsNull(Sum(Number3)),Null(),Sum(Number3)) as NumberSum3

And I only get 0, no Null for sums of Null elements

Re: Handling Null Values in GroupBy Sum Operations

JuMo — Wed, 17 Sep 2025 13:55:18 GMT

I cannot do that because sometime the sum is 0

Re: Handling Null Values in GroupBy Sum Operations

rwunderlich — Wed, 17 Sep 2025 15:48:36 GMT

The challenge is that Sum() treats null input as 0 by design. If you want to report null when all values of a dimension are null, you could do something like below. I'm using a "shadow" field where concat() collects the values. If the length of the shadow field is 0, then everything was null (or blank).

// Generate some test data

Data:

Load Dimension,

EmptyIsNull(Num1) as Num1,

EmptyIsNull(Num2) as Num2

Inline [

Dimension, Num1, Num2

A, 2, 4

A, 4, 5

B, , 8

B, , 7

C, 3,

C, 4,

D, 12, 34

];

Totals:

Load

Dimension,

if(Len(AllNum1) = 0, null(), SumNum1) as SumNum1,

if(Len(AllNum2) = 0, null(), SumNum2) as SumNum2

;

LOAD

Dimension,

Concat(Num1) as AllNum1,

Sum(Num1) as SumNum1,

Concat(Num2) as AllNum2,

Sum(Num2) as SumNum2

Resident Data

Group By Dimension;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Handling Null Values in GroupBy Sum Operations

Or — Wed, 17 Sep 2025 16:45:58 GMT

Plenty of good suggestions, but I'll throw in one more:

Sum(FieldName) * If(NullCount(FieldName)<>Count(FieldName),1)

The sum stays clean, and we multiply this by 1 if the count of nulls is not identical to the count of rows (including nulls). If the counts are identical, we multiply this by the else condition which, since it is not provided, is null(), thus nulling the entire expression.

Re: Handling Null Values in GroupBy Sum Operations

rwunderlich — Wed, 17 Sep 2025 17:40:44 GMT

@Or Great idea! I think there is a bug though. Count() returns count of non-null values, so your condition is always true. I'll amend my solution to use a modified version of yours as:

Totals:

LOAD

Dimension,

Sum(Num1) * If(Count(Num1) > 0, 1, null()) as SumNum1,

Sum(Num2) * If(Count(Num2) > 0, 1, null()) as SumNum2

Resident Data

Group By Dimension

;

-Rob

Re: Handling Null Values in GroupBy Sum Operations

Or — Wed, 17 Sep 2025 18:41:24 GMT

@rwunderlich Right. I misread the documentation, and I'm away from my Qlik so no testing.

I've always been a fan of using simple mathematical operations to avoid complicated logical ones, when it's feasible. I do strongly recommend documentation the logic, though, because I've noticed that some developers struggle to read this sort of thing. Including the explicit Null() in the else isn't necessary, but it does make for easier reading I suppose.

Re: Handling Null Values in GroupBy Sum Operations

rwunderlich — Wed, 17 Sep 2025 22:14:27 GMT

Come to think of it, this works as well and may be a bit clearer.

Totals:

LOAD

Dimension,

If(Count(Num1) > 0, Sum(Num1), null()) as SumNum1,

If(Count(Num2) > 0, Sum(Num2), null()) as SumNum2

Resident Data

Group By Dimension;

-Rob

Re: Handling Null Values in GroupBy Sum Operations

JuMo — Thu, 18 Sep 2025 07:43:32 GMT

Clean an elegant as usual !

Many thanks to everybody in this thread