Aggregating data from the last 3 related records created

[brian rich]

I'm working on a Parts system and need to determine the avarage lead time of the last three deliveries for that part - where lead time is the number of days from order placed to receipt of goods.

I can establish a relationship between parts and deliveries so i can see all the deliveries relating to a specific part in the portal. I have a calculated value on each delivery that gives me the lead time on that delivery. I can build a script that when a delivery is received for a part, it finds all deliveries for the part, gets the last three (or less), sums the lead times, and averages them and then stores this result back on the part record.

What I'd like to be able to do is to do this via a relationship, but I can't work out how to filter for the last 3 (or indeed any arbitary number of deliveries received). I'm also concerned that doing this through a relationship might give a big performance hit to the system, as there are 12,000 parts and 55,000 deliveries in the databases already.

Ideas and suggestions welcomed

Thanks

Brian

[jbante]

Assuming that you can make a direct relationship between Parts and Deliveries (i.e. each Delivery is for only one Part), you'd set up a typical relationship between the two based on Part Number: Part::id_Part = Delivery::id_Part, then you'd set the Delivery side of the relationship to be sorted descending based on date. You can then calculate Part::leadTimeAverage =

( GetNthRecord ( Delivery::leadTime ; 1 ) + GetNthRecord ( Delivery::leadTime ; 2 ) + GetNthRecord ( Delivery::leadTime ; 3 ) ) / 3

You are right to be concerned about the performance of this. I would stick with the scripted approach, but if you're willing to change the parameters of the problem a bit, I would calculate a different moving average instead. If the goal is to have a general sense of the average lead time for recent orders rather than the average of exactly the last 3, you only have to factor in the last average and the most recent Delivery when updating the average lead time. When a Delivery is received for a Part, the calculation for updating Part::leadTimeAverage becomes something like this instead:

Let ( ~weight = 1/3 ; ( 1 - ~weight ) * Part::leadTimeAverage + ~weight * Delivery::leadTime )

This way, you cut out the need to use data from all of the last several records each time, whether via scripted find or relationship. The value set for the ~weight variable can be adjusted for how strongly you want older or newer values to influence the updated average. (What you're doing now is actually a moving average, too, a "sliding window" moving average. The alternative above is an "exponential" moving average.)

If you're using this to schedule operations, it may be useful to track a percentile of the lead times rather than the average. For example, you might want a lead time estimate such that the actual lead times are less than the estimate 90% of the time, and go over only 10% of the time. The formula for this is similar in principle, but more complicated, requires an extra couple fields to support it, and can be "jumpy" at extreme percentiles (say, 99%) and high updating weights.

[jbante]

After writing that out, I had another idea more along the lines of the "sliding window" approach that would accomodate both calculating average and percentile lead times. You'd need one extra field in the Parts table to contain a return-delimited list of recent lead times, say, Parts::leadTimeList. When a Delivery is received for a Part, the lead time for that delivery is added to the top of the list, and the oldest lead time is truncated off the bottom. Something like so:

Substitute ( LeftValues ( List ( Delivery::leadTime ; Part::leadTimeList ) ; $valueCount ) & "<<END>>" ; ["¶<<END>>" ; ""]; ["<<END>>" ; ""] )

The Substitute () is there to get rid of a trailing return left by the LeftValues () function. $valueCount would be set with however many Deliveries of history you want to remember. To average, you'd add up the values, and divide by the number of values:

// Parts::leadTimeAverage =

Evaluate ( Substitute ( Part::leadTimeList ; ¶ ; " + " ) ) / ValueCount ( Part::leadTimeList )

This calculation is assuming that leadTimes are expressed as numbers in the list. If leadTime is expressed as a time or timestamp field rather than a number, you can get the right result with a combination of GetAsNumber () and GetAsTime[stamp] (). To get a percentile from this list, you'd use one of the many sorting custom functions available:

GetValue ( Sort ( Part::leadTimeList ) ; Ceiling ( $percentile * ValueCount ( Part::leadTimeList ) ) )

$percentile would be a number between 0 and 1. For example, to get the 90th percentile, $percentile would be 0.9. Make sure the Sort () function you use sorts the list as numbers, not as text. Also remember that, the more extreme the percentile you want to track, the more historical lead times you'll have to store. To get the 90th percentile, you have to store at least 10; to get the 95th percentile, you have to store at least 20; etc.

[brian rich]

Hi Jeremy

Thanks for those ideas, and the suggestions about weighting and percentiles. I'm using the scripted approach at the moment, but will look at building in the weighting and percentiles idea into the script. I'll let you know how it goes.

Thanks

Brian

[bTW, your BusinessDays() and BusinessHolidays() custom functions came in very useful for calculating lead times in the delivery . I've modified the BusinessHolidays() function for UK holidays, and added a dependency on the EasterDay() function from Johan Söderberg of Distingo, so I can add Good Friday and Easter Monday. Now if only there was a function to determine if we are going to have a holiday because it's the Queen's Diamond Jubilee, the BusinessHolidays() function will be complete. ]