Solved

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

Posted on 2012-03-22
1,412 Views
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.
0
Question by:bms22
• 11
• 7
• 3

LVL 100

Expert Comment

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
0

Author Comment

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.
0

LVL 100

Expert Comment

Are you limiting the data in the report to the 3 months?
If so then the average at the end will be correct.

mlmcc
0

Author Comment

No, the report actually contains data by month since the beginning of 2010.
0

LVL 34

Expert Comment

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
0

Author Comment

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.
0

LVL 100

Expert Comment

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
0

Author Comment

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
0

LVL 34

Expert Comment

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
0

Author Comment

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.
0

LVL 34

Expert Comment

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
0

Author Comment

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.
0

LVL 34

Expert Comment

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
0

Author Comment

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.
0

LVL 34

Accepted Solution

James0628 earned 400 total points
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
0

Author Comment

That work perfectly! Thanks very much for all your help on this.
0

Author Closing Comment

This solution works perfectly!
0

LVL 34

Expert Comment

You're welcome.  Glad I could help.

James
0

Author Comment

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.
0

LVL 34

Expert Comment

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
0

Author Comment

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
0

## Featured Post

### Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.