topic Comparing Data in a Straight table in QlikView

Comparing Data in a Straight table

Thu, 03 Dec 2009 19:16:57 GMT

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

Thu, 03 Dec 2009 19:28:09 GMT

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

Thu, 03 Dec 2009 19:44:41 GMT

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?

Comparing Data in a Straight table

Thu, 03 Dec 2009 20:04:23 GMT

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

Thu, 03 Dec 2009 20:29:20 GMT

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.

AW:Comparing Data in a Straight table

Thu, 03 Dec 2009 21:36:38 GMT

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

Thu, 03 Dec 2009 22:10:54 GMT

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

Thu, 03 Dec 2009 22:22:10 GMT

That's exactly what my expression does.

Try it out! 😉

Comparing Data in a Straight table

Miguel_Angel_Baeyens — Thu, 03 Dec 2009 23:22:16 GMT

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

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

Comparing Data in a Straight table

Fri, 04 Dec 2009 12:44:42 GMT

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

Fri, 04 Dec 2009 14:52:19 GMT

Hi Miguel,

Could you please give me the syntax for 8.5?

Thanks,