is it correct to write rangemax within if condition

[qvqfqlik]

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

[sunny_talwar]

What is the error do you see? Do you have a sample you can share to better understand your desired output?

[swuehl]

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.

[qvqfqlik]

I fixed this.

I was trying to display default value as when  the cuts sum is negative. Thanks a lot for looking into this.