Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

Handle NULL in calculation.

Hi guys,

Here's what my raw data looks like:

Capture.PNG

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
JustinDallas
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:

LOAD *,

If( SubOrdersString = 'NULLME', Null(), SubOrdersString ) AS  SubOrders

;

LOAD * Inline

[

Order, SubOrdersString, NetSales

    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

]

;

DROP FIELD SubOrdersString

;

EXIT Script

;

Capture44.PNG

15 Replies
roharoha
Valued Contributor III

Re: Handle NULL in calculation.

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

wanyunyang
Contributor

Re: Handle NULL in calculation.

I mean real null values

roharoha
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

wanyunyang
Contributor

Re: Handle NULL in calculation.

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

roharoha
Valued Contributor III

Re: Handle NULL in calculation.

try

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

wanyunyang
Contributor

Re: Handle NULL in calculation.

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

roharoha
Valued Contributor III

Re: Handle NULL in calculation.

what's the result of

SUM(Netsales)

Does it work?

wanyunyang
Contributor

Re: Handle NULL in calculation.

SUM(Netsales) works

COUNT(DISTINCT SubOrders) doesn't

roharoha
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()