Hi Expert,
Need support to get Sum Qty with Invoice wise. Example Data is given below 
Invoice No. - Articel Code - Qty. - Result require
INV123 - Toy - 50 - 80
INV123 - Book - 20
INV123 - pencil - 10
INV567 - Cake - 20 - 30
INV567 - Pen - 10
Could you guide me how i can use formula to retrive sum Qty data.
BR,
Sultan Khan
Please provide the results exactly as you are expecting to see them.
Noel
Hi Novel,
I require total sum of qty.
Result out came sould be =80 against invoice no. Inv123.
And
Inv567 =30
Invoice No. - Articel Code - Qty. - Result require
INV123 - Toy - 50 - 80
INV123 - Book - 20
INV123 - pencil - 10
INV567 - Cake - 20 - 30
INV567 - Pen - 10
Thank a lot in advance for your support.
BR
Sultan
This is as simple as creating a variable (I called mine Var Sum of Qty) with the following formula…
=Sum([Qty])
You could also add a break on InvoiceNo and Sum the Qty column.
The results of both methods are shown below.
1 Like
Hi Novel,
This is possible through invoice break.
As per requirement is result should be next column without break invoice wise.
BR
Sultan
Alright. Create a new variable called Var InvoiceNo Sum of Qty defined as follows…
=Sum([Qty]) In ([InvoiceNo])
The key with this formula is that it will give you the total for each InvoiceNo on all rows. I know this isn’t what you want, but I want to show the steps to get there.
Now add breaks on InvoiceNo and the new Var InvoiceNo Sum of Qty variable.
Almost there.
Next right-click on the table and choose “Breaks > Manage Breaks…”. We do not want break headers or footers for either break. For InvoiceNo we want to choose “Display all” under Duplicate values and for Var InvoiceNo Sum of Qty we want to choose “Display first”. They should look like this…
Finally in the Format Table properties check the “Table headers” checkbox. It got unchecked behind the scenes when the breaks were added.
And there you have it.
Noel
2 Likes
Dear Noel
I tried formula =[Var(Sum([Qty] In ([InvoiceNo]).
System is validate correct formula but all row showing total Qty instead of invoice Qty.
And =[Var Sum of Qty] In ([InvoiceNo]) is not working.
Pls guide, where I am going wrong.
BR
Sultan
I was making a faulty assumption. I have edited my previous response. The Var InvoiceNo Sum of Qty variable should have the following formula…
=Sum([Qty]) In ([InvoiceNo])
Give that a try.
Hi Noel,
Sorry to say, but still showing total qty. On each row instead of invoice wise sum.
BR,
Sultan
Is the final table in my response above what you require?
If so, please provide screenshots to show your formulas, breaks, and format table properties as I did in my example and perhaps I can guide you to your objective.
Noel
1 Like
