3 Replies Latest reply: Jun 20, 2016 11:45 PM by P K RSS

    is it correct to write rangemax within if condition

    P K

      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