Not tried this but it might work for you, Place in detail or group footer where your amount is displayed.
You might need to suppress for first 11 records
numbervar the_field := {table.field}; // your value field
numbervar array Accum;
numbervar Rolling_Period := 12;
if OnFirstRecord then
ReDim Accum [1]
else
ReDim Preserve Accum [UBound(Accum)+1];
Accum [UBound(Accum)] := the_field;
If UBound(Accum) <= Rolling_Period then
Sum(Accum) /Rolling_Period
Else
sum(Accum[(UBound(Accum) - (Rolling_Period-1)) to UBound(Accum)])/Rolling_Period;
Ian
Unfortunately it did not work. Below in yellow is the results using the new formula. I have found away to calculate the value but in a very convoluted way. For each month, I have created a formula where I save the months value. I then went and created a formula to sum the various values together. This gives me the correct values (shown in the "Correct result" column below), but I still have the Problem then on how I get to Show this in a line Chart and there must be a simplified way of doing this or?
Add comment