# Need to calculate 3-month moving average in Crystal Reports

I have data like the following that shows the number of days it took to issue a report.

January            Days to issue report
Report 1              54 days
Report 2              43 days
Report 3              67 days

February             Days to issue report
Report 4             23 days

March            Days to issue report
Report 5             54 days
Report 6             34 days
Report 7              45 days
Report 8             73 days

I want to calculate a 3-month moving average of the number of days to issue a report. The number would be calculated by averaging together the numbers in the “Days to issue report” column for Jan – Mar. The next month it would be the average of the numbers for Feb –Apr, etc.

I am a Crystal Reports novice. I would appreciate any help you could give. Thanks.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
How is the value calculated?

I believe it is a simpy a summary of  the value

Right click the value
Click INSERT --> SUMMARY
Set it for AVERAGE
Put it in the report footer

mlmcc
Author Commented:
Thanks. The problem is it needs to be a rolling average. So, at the end of March, it would be the average of all the values for Jan - Mar. At the end of April, it would be the average of all the values for Feb - Apr. At the end of May, it would be the average for Mar - May, etc. If I use a summary in the report footer, it averages all the values in the report and I need it to be a rolling average. Sorry to be slow on this, but I am a real novice.
Commented:
Are you limiting the data in the report to the 3 months?
If so then the average at the end will be correct.

mlmcc
Author Commented:
No, the report actually contains data by month since the beginning of 2010.
Commented:
Based on the date when you run the report, you want to include that month and the previous 2 months?

I'm assuming that you have a date field and a "Days to issue report" field.  If "Days to issue report" is actually a summary or calculation, you may have to use a different approach.

This is untested, but it seems like it would work (given the conditions mentioned above).

Create a formula like the following.  Let's call it report_count.

if {date field} >= DateSerial (Year (CurrentDate), Month (CurrentDate) - 2, 1) then
1

Create a formula like the following.  Let's call it report_days.

if {date field} >= DateSerial (Year (CurrentDate), Month (CurrentDate) - 2, 1) then
{Days to issue report}

If the data could include dates in the future (after the current month), and you don't want to include those in the average, the formulas would need to be changed to exclude those dates.

The idea is to use the first formula to get a count of the records that are dated in the last 3 months, and the second formula to get the totals days from those records.

Create a formula like the following (call it whatever you like) and put it wherever you want the average.

if Sum ({@report_count}) > 0 then
Sum ({@report_days}) / Sum ({@report_count})

James
Author Commented:
Thanks. The problem is that when I run the report, which has data back to the beginning of 2010, I would like the 3 month moving average to appear like this:

January            Days to issue report
Report 1              54 days
Report 2              43 days
Report 3              67 days
3-month avg        56 days

February             Days to issue report
Report 4             23 days
3-month avg       52 days

March            Days to issue report
Report 5             54 days
Report 6             34 days
Report 7              45 days
Report 8             73 days
3-month avg       61 days

It seems like somehow I would need to get the average to roll rather than just calculating the average for the past three months. I'm a real newbie here, so I'm probably missing something.
Commented:
WHen you run at the end of April will you show all the data for the year Jan-Apr or just Feb-Apr.

If all you include in the report is the last three months the the summary average will work.

If you are showing the full year of data but want an average for the last 3 months then formulas will be needed.

mlmcc
Author Commented:
The shows all the data since the beginning of 2010. For each month on the report (i.e. Jan 2010 through current date) I would like to show the 3-month rolling average as shown below.

January            Days to issue report
Report 1              54 days
Report 2              43 days
Report 3              67 days
3-month avg        56 days

February             Days to issue report
Report 4             23 days
3-month avg       52 days

March            Days to issue report
Report 5             54 days
Report 6             34 days
Report 7              45 days
Report 8             73 days
3-month avg       61 days
Commented:
Ah.  I thought you just wanted one average, for the last 3 months as of when you ran the report.

Is the report in ascending date order - Starting in 2010 and moving forward to today (or whatever end point you use)?  Or is it in reverse order, with the most recent months first?  Your examples are ascending, but I wanted to make sure.  If it was in reverse order, you'd have a real problem, because you need to use data from later in the report.  If it's in ascending date order, then I guess you'll need to use formulas to save the month totals in variables and then calculate the averages from those variables.

What about the first 2 months on the report, when you don't have a "last 3 months"?  Just do the averages for the first month and then the first 2 months?

James
Author Commented:
Yes, it is in ascending order starting with January 2010. For the first two months of 2010 we would just leave the 3-month average field blank since we don't have the data. Thanks.
Commented:
I'm assuming that the "Days to issue report" column is a field or a simple summary, as opposed to being calculated in some way; and that the report is grouped on a date field, by the month.

Create a formula like the following (call it whatever you like) and put it in the report header:

WhilePrintingRecords;

Global NumberVar this_month;
Global NumberVar one_month_ago;
Global NumberVar two_months_ago;

""

The "" at the end is simply so the formula doesn't produce any visible output on the report.  You could also suppress the field, or the section.

Create a formula like the following (call it whatever you like) and put it in your month group footer, where you want to see the average:

WhilePrintingRecords;

Global NumberVar this_month;
Global NumberVar one_month_ago;
Global NumberVar two_months_ago;

two_months_ago := one_month_ago;
one_month_ago := this_month;
this_month := Sum ({Days to issue report field}, {date group field},  "monthly");

(this_month + one_month_ago + two_months_ago) / 3

I think that should give you your averages.  At least, it seems to work here.  For the first 2 months, I think I'd use suppression on that field.  Will there always be records for every month?  If so, you could compare the date to the minimum date on the report and suppress based on that (if the date is not at least 2 months after the minimum date, suppress the field).

James
Author Commented:
Well, the formula works, but what it is doing is adding up the "Days to Issue Report" field for each of the three months, adding those totals together, and then dividing by 3. So, using the example in my 3/28/2012 post, it is doing the following:

((54+43+67) + (23) + (54+34+45+73))/3 = 131

Instead of dividing by 3, we need to divide by the number of reports issued (which would be 8 in the example). Dividing by 8 gives the correct average of 49.
Commented:
Ah.  I thought you wanted the average of the totals for the last 3 months.  For an average for the reports in the last 3 months, try this.

Change the first formula (in the report header) to:

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;

""

Change the second formula to:

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;

days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({Days to issue report field}, {date group field},  "monthly");

reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({Report ID field}, {date group field},  "monthly");

(days_this_month + days_one_month_ago + days_two_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago)

See if that works.  If the calculation still seems off, you could try using Count instead of DistinctCount.  And if you want to check the count for each month, or anything else, you can just comment out the last two lines (the average calculation) and replace them with the variable that you want to check (eg. reports_this_month for the count for the current month).

James
Author Commented:
That works! Thanks very much! The one remaining problem I'm having is that some months have no data. Going back to the example in my 3/28/2012 post, if there were no data for February, somehow we would have to recognize that and calculate the 3-month average as just the January and March data.
Commented:
If there is no data for a month, it won't show on the report.  I'm assuming that that is OK.  If you want the missing months to still show on the report, that's a separate problem.

To change the average calculations, try this.

Change the first formula (report header) to this:

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;

Global DateTimeVar last_month;

last_month := #01/01/1900#;

""

Change the second formula to this:

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;

Global DateTimeVar last_month;

if last_month = #01/01/1900# or DateDiff ("m", last_month, {date group field}) = 1 then
(
days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({Days to issue report field}, {date group field},  "monthly");

reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({Report ID field}, {date group field},  "monthly")
)
else
if DateDiff ("m", last_month, {date group field}) = 2 then
(
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({Days to issue report field}, {date group field},  "monthly");

reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Report ID field}, {date group field},  "monthly")
)
else  // current and previous month are more than 2 months apart
(
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({Days to issue report field}, {date group field},  "monthly");

reports_two_months_ago := 0;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Report ID field}, {date group field},  "monthly")
);

last_month := {date group field};

(days_this_month + days_one_month_ago + days_two_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago)

Basically, I added the last_month variable, which is initially set to 01/01/1900, which tells the second formula that this is the first month on the report.  Otherwise, the second formula compares the current month in the report to the previous one (in last_month).
If they're one month apart, the values are shifted like normal.
If they're 2 months apart, the previous values (in *_this_month) are moved to *_two_months_ago, and the *_one_month_ago variables are set to 0.
Otherwise, the *_one_month_ago and *_two_months_ago variables are all set to 0.

In case you're wondering, DateDiff just looks at the date component that you're checking, so, in this case, it just compares the months in the two dates.  It doesn't care about the days.  So, for example, DateDiff ("m", #03/31/2012#, #04/01/2012#) is 1, even though the dates are actually only 1 day apart.

James

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
That work perfectly! Thanks very much for all your help on this.
Author Commented:
This solution works perfectly!
Commented:
You're welcome.  Glad I could help.

James
Author Commented:
Sorry, but I am now trying to use the same methodology to compute a 6-month moving average. I tried updating the formula, but keep getting an error. Would you mind posting what the formula would be for a six-month moving average? Thanks.
Commented:
This probably should have been a new question.  FWIW, there used to be a way to "ask a related question" when looking at an old question.  The new question would include a link to the old one and the people that participated in the old question would be notified about the new one.  But I don't know if this new EE format still has that option.  If not, you could have started a new question, and added a post here (and I'd be notified about that new post) with a link to the new question.  Anyway ...

What kind of error?

Basically, just add variables for the other 3 months to both formulas, and add tests for the current and previous dates being 3, 4 and 5 months apart to the second formula.  It seems like that should be all there is to it.  If you post what you have, and the error, maybe I can spot the problem.

James
Author Commented:
Thanks. I'm new to all this and still learning the protocols. I posted a new question:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27669507.html
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.