10 Replies Latest reply: Dec 4, 2009 9:52 AM by Jacodpl

# Comparing Data in a Straight table

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,

• ###### Comparing Data in a Straight table

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.

• ###### Comparing Data in a Straight table

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?

• ###### Comparing Data in a Straight table

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?

• ###### Comparing Data in a Straight table

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.

• ###### AW:Comparing Data in a Straight table

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

• ###### AW:Comparing Data in a Straight table

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!

• ###### AW:Re: AW:Comparing Data in a Straight table

That's exactly what my expression does.

Try it out! ;)

• ###### Comparing Data in a Straight table

I'd rather use set analysis for that (untested):

`Sum( {\$< InvNumber =- DeliveryNote >} Sales Order Qty)`

• ###### Comparing Data in a Straight table

Thanks. I will try both and see if I can get this to work. Will update you on my result.

• ###### Comparing Data in a Straight table

Hi Miguel,

Could you please give me the syntax for 8.5?

Thanks,