Handle NULL in calculation.

[wanyunyang]

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!

[wanyunyang]

thanks anyway

[JustinDallas · Accepted solution]

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
;

[wanyunyang]

Worked! Thanks!

[Anonymous]

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

[isingh30]

Please check this. Let me know.

Thanks.

[wanyunyang]

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