most recent + ignore odd records

[lyderic]

Hello,

I have some transactions data (purchases of some products made at a certain date). I need to pull from the data the most recent price paid for each product. That's not hard, but here are a couple of caveats:

1. there can be multiple transactions on the same date for the same product --> in that case, let's just take the average. I'd use the Summary tool: Group by Product, Max Invoice Date, Average Quantity, Average Total Cost. Then Formula (Avg_Total Cost / Avg_Quantity) to return the Unit Cost. 

2. in these multiple transactions on the same day, there could be some odd ones that we need to ignore from the average calculation (see line in red: someone fat-fingered the total cost). Here, I'm a bit stuck and I'd love some help. Any suggestions?

Thank you all for your help.

[Qiu]

@lyderic 

I think we need to give a quantitive definition on "Fat-finger"😁
I tired with average cost difference is bigger than 2 with last row of data as below.

[RobertOdera]

Hi, @lyderic 

Kindly consider generating a proxy that identifies "fat-fingered" entries:

1. compare the ones, tens, and hundred digits for all entries for the same Product Name

2. if the digits are not within a determined tolerance of the (median, mode, or average) of the prior entries for the same date = flag

3. discard flagged rows

I hope this helps. Cheers!