Sum - Duplicate Values

[whiteninja]

Itemkey Locationkey Itemdescription Qtyshipped Qty Gallons Qtyonhand
D203-STD-245-001L
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 24.00 6.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 24.00 6.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 6.00 1.50 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 6.00 1.50 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L -1.00 -0.25 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 6.00 1.50 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 12.00 3.00 788.00
D203-STD-245-001L DECNO SUPREME EMULSION WHITE 0.946L 72.00 18.00 788.00
D203-STD-245-001L DECSO SUPREME EMULSION WHITE 0.946L 1.00 0.25 131.00
D203-STD-245-001L DECSO SUPREME EMULSION WHITE 0.946L 12.00 3.00 131.00
D203-STD-245-001L DECSO SUPREME EMULSION WHITE 0.946L 6.00 1.50 131.00
D203-STD-245-001L DECSO SUPREME EMULSION WHITE 0.946L 12.00 3.00 131.00
60.00 10,786.00 - should be 919

Guys I a try to get a total for qty gallons (60) and qty on hand which should be 788 + 131 but values are on every line.
Any idea how I can SUM Quantity on hand without have duplicate values. Thanks.

 

[pmax9999]

You could try the following. Without understanding the way the data is structured I can't be certain it will work for all circumstances but it will work for the sample data provide. From what I can see from the sample provide, it looks like the Locationkey is the column that indicates the distinction between the amounts to sum.

Therefore, rather than use a simple Summary (Sum), use a Running Total as follows:

[pre]
Field to Summarise: {Table.Qtyonhand}
Evaluate: On Change of Field {Table.Locationkey}
Reset: Never (or on change of {Table.Itemkey} if records are grouped that way)
[/Pre]
Then remove {Table.Qtyonhand} from the Details section and place the new Running Total in the Report Footer / Group Footer as appropriate.

Hope this helps.

Cheers
Pete

 

[whiteninja]

Thanks Pete - works like a charm!!!