Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
New to Qlikview Using Sage, want to pull stock info where if the back order qty + allocated value > physical qty then return the physical qty or if the back order qty + allocated value< physical qty then return the back order qty + allocated
Help?
=if((stockm.back_order_qty + stockm.allocated_qty) >stockm.physical_qty,stockm.physical_qty,if(stockm.physical_qty <(stockm.back_order_qty + stockm.allocated_qty),(stockm.back_order_qty + stockm.allocated_qty))
Like this?
=if((stockm.back_order_qty + stockm.allocated_qty) >stockm.physical_qty,if(stockm.physical_qty <(stockm.back_order_qty + stockm.allocated_qty),(stockm.back_order_qty + stockm.allocated_qty))
Thanks Daniel, Have added this to a calculated dimension in my chart but it still errors with 'Error in Calculation'. I did try the below
=if((stockm.back_order_qty + stockm.allocated_qty) > stockm.physical_qty,stockm.physical_qty,(stockm.back_order_qty + stockm.allocated_qty))
but rather than returning the Physical and BO +Alloc it returned only the BO + Alloc.
Any further suggestions.
Thanks
The problem is you can't use some kind of switch, so you have to do nested if else, and the problem is that is very easy to get wrong in any parameter and you'll turn crazy if you have some mistake.
What I usually do is to build my nested if else sentences in this web page that helps you to do that :
http://www.qlikblog.at/tools/Nested-If-Generator.html
Hope that it helps!!
Instaed of a calculated dimension use an ordinary expression,
for example something like this: (you need to ad some dimonsion also)
sum(if((stockm.back_order_qty + stockm.allocated_qty) >stockm.physical_qty,
if(stockm.physical_qty <(stockm.back_order_qty + stockm.allocated_qty),(stockm.back_order_qty + stockm.allocated_qty)))