Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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,

10 Replies
Not applicable
Author

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.

Not applicable
Author

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?

Not applicable
Author

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?

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

That's exactly what my expression does.

Try it out! 😉

Miguel_Angel_Baeyens

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

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


Not applicable
Author

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