15 Replies Latest reply: Feb 26, 2018 2:54 PM by Wanyun Yang

# Handle NULL in calculation.

Hi guys,

Here's what my raw data looks like:

Order A is divided into sub orders a,b,NULL. I want to calculate the net sales of A, which should be 2+3+4=9. I wrote this formula in Qlik Sense:

SUM(Netsales)/COUNT(DISTINCT SubOrders)

But the result is NULL. How can I fix this?

Thanks for help!

• ###### Re: Handle NULL in calculation.

... that should work,if you have real null() values. I think that there's another problem...

• ###### Re: Handle NULL in calculation.

I mean real null values

• ###### Re: Handle NULL in calculation.

you'll have to create a small example... I guess that the problem is the sum() function, perhaps the data is non numerical

• ###### Re: Handle NULL in calculation.

In my example above, the (NULL) is real null value in database. The calculation result is a dash.

• ###### Re: Handle NULL in calculation.

try

SUM(num#(Netsales))/COUNT(DISTINCT SubOrders)

• ###### Re: Handle NULL in calculation.

Doesn't work...the data is numerical.

• ###### Re: Handle NULL in calculation.

what's the result of

SUM(Netsales)

Does it work?

• ###### Re: Handle NULL in calculation.

SUM(Netsales) works

COUNT(DISTINCT SubOrders) doesn't

• ###### Re: Handle NULL in calculation.

... then I'm out of ideas as long as you're sure that the field SubOrders contains values <> null()

• ###### Re: Handle NULL in calculation.

thanks anyway

• ###### Re: Handle NULL in calculation.

When you load your data, can you do an "If( IsNull(MyField), 1 )" test?  Because I've got this test data and it appears to be working for me.

```OrderStuff:
;
[
A, a, 2
A, a, 3
A, a, 4
A, b, 2
A, b, 3
A, b, 4
A, 'NULLME', 2
A, 'NULLME', 3
A, 'NULLME', 4
]
;

;

EXIT Script
;
```

• ###### Re: Handle NULL in calculation.

Worked! Thanks!

• ###### Re: Handle NULL in calculation.

I still don't understand qhy you got a dash as return value...

• ###### Re: Handle NULL in calculation.

I think it doesn't treat all the NULLs as a group.

• ###### Re: Handle NULL in calculation.

Please check this. Let me know.

Thanks.