Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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,

Tags (1)
10 Replies
Not applicable

Comparing Data in a Straight table

Quick and dirty solution Smiley Happy

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

Comparing Data in a Straight table

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

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?

Not applicable

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.

Please find attached my structure.

Not applicable

AW:Comparing Data in a Straight table

Hi,

if I understand you correct, then this should work Smiley Wink

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

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!

Not applicable

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

That's exactly what my expression does.

Try it out! Smiley Wink

MVP
MVP

Comparing Data in a Straight table

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

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


Not applicable

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.

Community Browser