topic Re: is it correct to write rangemax within if condition in QlikView

is it correct to write rangemax within if condition

qvqfqlik — Mon, 20 Jun 2016 22:03:32 GMT

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

Re: is it correct to write rangemax within if condition

sunny_talwar — Mon, 20 Jun 2016 22:16:24 GMT

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

Re: is it correct to write rangemax within if condition

swuehl — Mon, 20 Jun 2016 22:19:59 GMT

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.

Re: is it correct to write rangemax within if condition

qvqfqlik — Tue, 21 Jun 2016 03:45:21 GMT

I fixed this.

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