Rolling 7 days

You ought to be able to get it using a formula field to select, saying something like {your.date} + 6 if you want future dates. Subtract for past.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

Hi Madawc,

I dont understand.

Here is the code from the thread i linked to:

I have already changed the firlend name to the one i want to 'roll' and i changed the 'Rolling Period' variable to 7. All i need is for someone to help me figure out what i need to change so that it rolls the current field and previous 6 instead of ignoring the current field and rolling the previous 7.

-------------------

numbervar the_field := {v_d_FTR_from_CLI.CALLS_1};
numbervar array Accum;
numbervar Rolling_Period := 7;

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) - the_field
Else
sum(Accum[(UBound(Accum) - (Rolling_Period-1)) to UBound(Accum)]) - the_field;

Try this variation on bdreed's formula:

numbervar the_field := {table.field};
numbervar array Accum;
numbervar Rolling_Period := 7;

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)
Else
sum(Accum[(UBound(Accum) - (Rolling_Period-1)) to UBound(Accum)]);

-LB

Thanks lbass that worked.

I created the formula for 2 fields that i wanted to average.

Problem is now i cannot graph it.

Its for First Call Resolution.

I created one rolling formula for the number of customers that called once and another rolling formula for the total number of calls.

I can display the formula result in the detail rows but i am unable to chart it.

Should i have built a database view instead of producing the rolling results in crystal?

Eddie

I was able to chart rolling sum formula, so maybe you can adapt the method. I changed the original formula to:

numbervar the_field := {table.field};
numbervar array Accum;
numbervar Rolling_Period := 7;
numbervar x;
shared stringvar showval;
shared stringvar onchgof;

if OnFirstRecord then
ReDim Accum [1] else
ReDim Preserve Accum [UBound(Accum)+1];
Accum [UBound(Accum)] := the_field;
If UBound(Accum) <= Rolling_Period then
x := Sum(Accum) Else
x := sum(Accum[(UBound(Accum) - (Rolling_Period-1)) to UBound(Accum)]);
showval := showval + totext(x,0,"") + "^";
onchgof := onchgof + totext({table.uniquerowID},0,"") + "^";
x

Use a field that is unique to each row for the onchgof variable.

Then I added another (suppressed) formula {@onchgof} to the detail section:

whileprintingrecords;
shared stringvar onchgof;

Then in the report footer I added a subreport that is linked on {@onchgof} field. Ignore the field that shows up as the matching field for the subreport. In the subreport, go to report->record selection formula->record and change the formula to:

totext({table.uniquerowID},0,"") = split({?Pm-@onchgof},"^")

Then suppress all sections except the subreport footer.

Create a formula {@showval}:

whileprintingrecords;
shared stringvar showval;
val(split(showval,"^")[recordnumber])

Do not place this formula on the report or it will not be available for the chart. Insert an advanced chart in the subreport footer and add {@showval} as the summary field and check "Do not summarize". In the "On change of" area, change the dropdown to read "For each record". Then click OK.

This tested out accurately for me, but I was limited in the number of records I could use (I guess 1000), so that is a limitation.

-LB

Hi - Thats great, but for 1 rolling number, the number of customers that called once, but if i repeat the process for the 2nd rolling rolling, the total number of calls, how does that work?

Ideally i would like to divide the 1st rolling number wth the secod to produce a First Call Resultion line graph

Try setting showval := showval + the calculation in a new formula.

-LB

awesome, thanks!!

The graph now works.

I have one more request.

Is it possible to show the date across the bottom?

in this case the date is the field i used for the variable onchgof.

If you have the chart set up "for each record", add the date field (not the onchgof formula) as the field in the box below "for each record"--but leave the heading as "for each record." Since you have already used the {@onchgof} formula in the record selection of the subreport, only the correct dates will appear in the chart.

-LB

Thanks lbass. Thats worked.

I actually did that before, but i panicked because to double check the results, i added markers and the date value that popped up with the marker was no where near the the date on the x-axis.

But by checking the 'result' on the marker, it does match to the result [that i have hidden in the main report]

Just thought i'd share that with you.

Thanks

Eddie