# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## 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!

1 Solution

Accepted Solutions
Valued Contributor

## 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
;
```

15 Replies
Valued Contributor III

## Re: Handle NULL in calculation.

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

Contributor

## Re: Handle NULL in calculation.

I mean real null values

Valued Contributor III

## 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

Contributor

## Re: Handle NULL in calculation.

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

Valued Contributor III

## Re: Handle NULL in calculation.

try

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

Contributor

## Re: Handle NULL in calculation.

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

Valued Contributor III

## Re: Handle NULL in calculation.

what's the result of

SUM(Netsales)

Does it work?

Contributor

## Re: Handle NULL in calculation.

SUM(Netsales) works

COUNT(DISTINCT SubOrders) doesn't

Valued Contributor III

## Re: Handle NULL in calculation.

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