Hi All, is it possible to calculate the average price from the last three purchase orders for a stock item?

I'm just not sure how to obtain and evaluate just the last three prices.

Additionally some low use items may only have one price recorded for that item which makes it a bit harder.

Thanks Peter :-)

Edited February 9, 200718 yr by Guest

Well if your PO has a related line items table between the products and the PO, you can from the products side make a relationship sorted by date Descending. Then you can use an Unstored calc of:

cLastThree [repeating calc of 3 reps] =

GetNthRecord ( Extend ( ProducttoLineItem::price ); Get ( CalculationRepetitionNumber ) )

ProdAvg = Average ( cLastThree )

In regards to what you mentioned as "low use items" having only one price, it is okay because an average of only one number is the number.

You could just as easily get them from the other end (i.e. without forced sorting):

Let ( [

n = 3 ;

m = Count ( Child::ParentID ) ;

i = Get ( CalculationRepetitionNumber ) 

] ; 

Case ( i ≤ Min ( 3 ; m)  ; GetNthRecord ( Extend ( Child::Value ) ; m - i + 1 ) )

)

Thanks for the ideas, I'll try them and see how I go.

The individual purchase order line items use the StockID as a secondary key so are related to the Stock Items.

Thanks Peter :-)

Edited February 10, 200718 yr by Guest

Hi Comment, I've tried using your suggestion but it seems to only evaluate the last P/O Line Item record created for each stock item?

I've tried to figure out why this is but I'm not very familiar with some of the functions used. Could you post a small explanation about each step so I can learn from this?

Thanks Peter :-)

Edited February 10, 200718 yr by Guest

Here's a working demo, with comments added to the calculation.

AverageLastN.fp7.zip

Many Thanks Comment, it turned out that I only had one repetition specified on the main calculation field so it only showed the most recent value.

Cheers Peter :-)