Why, when Item Qty is 1, is sum(Item Qty) showing 2?

[#1]

I have a large sales data table. I noticed that when I summed the Item Qty, I see the actual Item Qty doubled when I add the expression sum(Item Qty) to it. The picture below shows a snapshot of the table (note: for this purpose, the ONLY fields I've selected in my script are TxnID, Item Number, and Item Qty)

As you can see, in each case, the Item Qty is doubled from the value shown in the table box. Can anyone tell me why this might be happening?

thanks!

[Anil_Babu_Samineni]

Because you have 2 Items for each Transaction may be the cause? can you remove Item Number from object and check

[Chanty4u · Accepted solution]

am not sure, but try

sum(Distinct [Item Qty])   

if not check with your data is there any other filed is mapping to that? 

put exit script each table and check the front end.

Best

Chanty

[#4]

In each transaction, there can be multiple items (you buy the mattress, frame, and sheets, for example). Each item is identified by a unique item number. Each item should have a Item Quantity affixed to it IN THE CONTEXT OF THE TRANSACTION. E.g. one person might buy one pillow, but in another transaction, another person might buy two pillows. So transactions look like this:

Transaction e56745736

• Item Number: 2607 Item Qty: 1
• Item Number: 2945 Item Qty: 2

Transaction f45673574

• Item Number: 2607 Item Qty: 4
• Item Number: 3297 Item Qty: 1

etc.

So, adding ItemQty for each ItemNumber should give me the total number of, e.g., king size pillows sold. But, as I noted, it is doubling the single items, and the count is off. Why would it show sum(Item Qty) as 2, when the actual Item Qty number is 1?

My script is simply:

LOAD

TxnID,

ItemNumber,

Item Qty

From

Datafile

so there are no other fields to worry about.

[#5]

If you check my reply above, you'll see my script. There are no other tables loaded. I should note: this happens all the time if Item Qty is 1, but only some of the time if the Item Qty is > 1, as shown in the snip below:

For example look at the transaction where the Sum Item Qty is 14; the corresponding number from the table box is 7. In fact, if you look at the four transactions clustered around the 14 value, you can see that all of those are doubled from the table box value, but the transactions immediately above and immediately below are not doubled! And it doesn't seem to related to any Item Number, as 2728 is sometimes doubled, and sometimes not. This is very strange!

[#6]

However, I did try as you suggested, and it seems to work!

Now, all I'd like is to understand is why....

thanks for your help!

[Chanty4u]

cool it worked.