Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am very new to Qlikview so please point me in the right direction. I have a straight table:
Dimentions:
Code, Description,InvNumber,DeliveryNote
Expressions:
Items = Sum(Sales Order Qty) - Sum(Credit Note Qty)
Will it be possible to only subtract the credit note qty if the InvNumber = DeliveryNote in the selection, else ignore credit note qty. I've attached a print screen.
Thanks,
Quick and dirty solution 🙂
Sum(Sales Order Qty) -Sum ( if(invNumber = DeliveryNote,Credit Note Qty,0))
if invNumber = DeliveryNote, the formula will calculate: Sales Order Qty - Credit Note Qty
else: Sales Order Qty - 0
I'm sure, there are better solutions, but this was the easiest one I've been able to think of.
Thanks for the quick reply.
If you look at the pic attached you will see that the InvNumber of line 2 matches the DeliveryNote of line 6, will it be possible to compare that way and then subtract if any match?
I'm not sure, but it should work.
If i didn't misunderstand you, you are trying to show the itemstock after a delivery, right?
Is it possible to show the structure ( Ctrl+t) of your File?
They use this report to pick stock .
In the example the invoice(TR015863) was credited(CR002900) and it reflected on the total but somtimes they only credit the item a week later so you will have the credit but the invoice is not in the selection and it still subtracts from the total.
Please find attached my structure.
Hi,
if I understand you correct, then this should work 😉
Sum([Sales Order Qty]) - Sum(if(fieldindex('InvNumber', DeliveryNote) > 0, [Credit Note Qty], 0))
It will only subtract the credit if there is an invoice in the selection..
greeting
Let me try and explain myself in more detail.
In the image below there is a InvNumber(TR015863) and a DeliveryNote that match, so it subtracts from the total, but if the InvNumber(TR015863) was not there then InvNumber(CR002900) shouldn't be subtracted from the total.
Thanks for the help guys!
That's exactly what my expression does.
Try it out! 😉
I'd rather use set analysis for that (untested):
Sum( {$< InvNumber =- DeliveryNote >} Sales Order Qty)
Thanks. I will try both and see if I can get this to work. Will update you on my result.