Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
;
... that should work,if you have real null() values. I think that there's another problem...
I mean real null values
you'll have to create a small example... I guess that the problem is the sum() function, perhaps the data is non numerical
In my example above, the (NULL) is real null value in database. The calculation result is a dash.
try
SUM(num#(Netsales))/COUNT(DISTINCT SubOrders)
Doesn't work...the data is numerical.
what's the result of
SUM(Netsales)
Does it work?
SUM(Netsales) works
COUNT(DISTINCT SubOrders) doesn't
... then I'm out of ideas as long as you're sure that the field SubOrders contains values <> null()