Weighted Moving Average

[piepmeier]

I am currently plotting the % On Time performance for shipments on a daily basis. I want to add a trend line showing a weighted moving average of this value (takes into account days with large volumes). The moving average trendline option in CR 8.5 is only a moving average. I only want to include the last 15 days in my calculation for my trendline so it would be (total on time items for last 15 days/total items shipped for last 15 days). Any thoughts on how to do this?

 

[Ngolem]

the moving average is easy enough to do but need some help working out the average value...perhaps an example would help...I will invent one here and see if you think i understand the problem

let me create some data

day total shipments no. on-time Weighttotal

1 100 25 2500
2 50 40 200
3 80 60 4800
4 70 30 2100
5 110 50 5500
----- ------ ------
totals 410 205 15100

for 5 days:
Average on-time shipments/day = 205/5 = 41
weighted average on-time shipments/day = 15100/410 = 36.9

Is this what you are looking for?

If the answer is yes then I'll show you how this is calculated..otherwise...using a simple example as I did above .... show me what you are looking for...
Jim Broadbent

 

[piepmeier]

I am simply doing 205/410 or 50% on time which is not the same as the average of the average for each day (this is what the moving average trendline does in CR). For a five day moving average, day 1 would drop off the next day and you would do the same calculation for days 2-6.

Thanks, Craig

 

[Ngolem]

that is not really a weighted average as I understand it...but that is fine if that is what you are looking for.

A weighted average would apply more emphasis on on-time deliveries on busy days relative to not so busy days.

OK so now I know what you are looking for let us tackle the problem at hand...the result you want is actually easier to do.

What we will do is simply create 2 arrays one for total shipments/day and the other for on-time shipments that day.
Then we will treat them like a conveyor belt and drop off the oldest data at one end and add a new data at the other

********************************************************

@Initialization (placed suppressed in group header
prior to the group for "day&quot
WhilePrintingRecords;
//initialize to 15 elements...one per day
if not inrepeatedGroupHeader then
(
numberVar array TLShip := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar array OTShip := [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
numberVar DayCount := 0;
);

********************************************************

@CalcAverages (detail Section - may or may not suppressed )

WhilePrintingRecords;
numberVar array TLShip ;
numberVar array OTShip ;
numberVar DayCount ;
numberVar TotalShip := 0;
numberVar TotalOnTime := 0;
numbervar AVG_OnTime;
Numbervar WtAVG_OnTime;
numbervar m;

DayCount := Daycount + 1;

for m := 14 to 1 step -1 do
(
TLShip[m+1] := TLShip[m];
OTShip[m+1] := OTShip[m];
);

TLShip[1] := {Table.TotalShipments};
OTShip[1] := {Table.OnTimeShipments};

for m := 1 to 15 do
(
TotalShip := TotalShip + TLShip[m];
TotalOnTime := TotalOnTime + OTShip[m];
);

AVG_OnTime := TotalOnTime / 15;
WtAVG_OnTime := TotalOnTime / TotalShip;

you can now display these values in this formula or create an additional formula for display and suppress this one

Use DayCount variable to conditionally suppress the averages (either the entire section or specific formulas)until the total is greater than or equal to 15.

Hope this helps Jim Broadbent