Solved: Re: Handle NULL in calculation. - Qlik Community

wanyunyang · ‎2018-02-26

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!

JustinDallas · ‎2018-02-26

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
;

View solution in original post

Anonymous · ‎2018-02-26

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

wanyunyang · ‎2018-02-26

I mean real null values

Anonymous · ‎2018-02-26

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

wanyunyang · ‎2018-02-26

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

Anonymous · ‎2018-02-26

try

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

wanyunyang · ‎2018-02-26

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

Anonymous · ‎2018-02-26

what's the result of

SUM(Netsales)

Does it work?

wanyunyang · ‎2018-02-26

SUM(Netsales) works

COUNT(DISTINCT SubOrders) doesn't

Anonymous · ‎2018-02-26

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