Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here , for a material I am showing default zero, when sum(orders)-sum(filled) is negative.
also should be zero when sum(order) and sum(filled) values are same but at different line/row level for same material
if(sum(CS_CUT_QTY) > 0, if(sum(CS_CUT_QTY) <> (sum(CS_ORD_QTY) - sum(CS_FILL_QTY)), (sum(CS_ORD_QTY) - sum(CS_FILL_QTY)),sum(CS_CUT_QTY)),0) AS CS_CUT_QTY
above logic works, when sum(orders) and sum(filled) are same but at different line/row item level. and it also works when when sum(orders)-sum(filled) is negative but at single row lvl.
But this does not work when sum(filled) > sum(orders) at different line/row item lvls.
I tried the below logic, but it throws error.
if(sum(cuts) > 0, if(sum(cuts) <> (sum(orders) - sum(filled)), rangemax(0, rangesum(orders, -1* filled)),sum(cuts)),0) AS cuts
please help, Thankyou
What is the error do you see? Do you have a sample you can share to better understand your desired output?
You need to embed all fields not listed in the GROUP BY clause field list in aggregation functions, and Rangesum() is not a aggregation function in that sense (aggregating across field dimension).
That's probably why you are getting an error.
I haven't really understood what your trying to achieve, or how your data looks like, so some sample input records would definitely help to understand that.
I fixed this.
I was trying to display default value as when the cuts sum is negative. Thanks a lot for looking into this.