AnsweredAssumed Answered

Strange Filtering Calculation Question

Question asked by twelvetens on Jan 8, 2016
Latest reply on Jan 12, 2016 by twelvetens

Been banging my head against this one for a while, and can't quite get it sorted, so hopefully someone with a finer mind than mine can help!

 

I have a list of dated historical orders, with quantities, and costs per item...

 

QtyCost Per ItemOrder Date
1008.0008/01/2016
2007.0020/12/2015
1008.2010/11/2015
1008.0023/09/2015

 

The number of entries in the historical orders table above can obviously range from 0 up to a few hundred.

 

The user wants to find out what the mean average price per item has been for the past X number of items purchased, across all historical orders, when sorted by order date (descending).

 

So, for the historical orders above, if the user wanted to look at the last 350 orders, we'd be looking for...

 

100 X 8.00 +

200 X 7.00 +

50   X 8.20     <---- 50 is used as opposed to 100, as there is only 50 remaining from the specified 350 after the previous two orders...

 

/ 350

 

= 7.457

 

 

Caveats:

This needs to be done as part of a calculation, as opposed to a looping script (if possible). I'm guessing this requires a recursive funciton of some sort?

 

I've tried looking through various custom function sites, but I can't really articulate what I'm after above into a meaningful search term, so I'm a bit stuck!

 

ANY HELP GRATEFULLY RECEIVED!

Outcomes