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

I am trying to calculate a 6-month moving average. The following formula works fine if there are no missing months. But is some month's data are missing, the results are not correct. The formula was originally written for a 3-month moving average and I wasn't sure how to adjust the formula to account for missing months when using it for a 6-month moving average. Thanks.

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;
Global NumberVar days_three_months_ago;
Global NumberVar days_four_months_ago;
Global NumberVar days_five_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;
Global NumberVar reports_three_months_ago;
Global NumberVar reports_four_months_ago;
Global NumberVar reports_five_months_ago;

Global DateTimeVar last_month;

last_month := #01/01/1900#;

""

In group footer:

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;
Global NumberVar days_three_months_ago;
Global NumberVar days_four_months_ago;
Global NumberVar days_five_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;
Global NumberVar reports_three_months_ago;
Global NumberVar reports_four_months_ago;
Global NumberVar reports_five_months_ago;

Global DateTimeVar last_month;

if last_month = #01/01/1900# or DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 1 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 2 then
(
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "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 ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_two_months_ago := 0;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
);

last_month := {Form__Project.FinalRptDt};

(days_this_month + days_one_month_ago + days_two_months_ago + days_three_months_ago + days_four_months_ago + days_five_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago + reports_three_months_ago + reports_four_months_ago + reports_five_months_ago)
###### 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:
First you need to set months ago 5-3 in all the clauses.

You also have to account for month differences of 3,4,5

else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 2 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 3 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_this_month;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_this_month;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
Etc for 4 and 5

mlmcc
0
Commented:
What he said.  :-)

Using pseudo-code, for simplicity, you had this:

if last_month = #01/01/1900# or DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 1 then
(
<set the variables for the last 6 months>
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 2 then
(
<set the variables for the last 3 months, but leave the previous month 0>
)
else  // current and previous month are more than 2 months apart
(
<set the variables for the last 3 months, but leave the previous two months 0>
);

The first part seems fine, but then in the next two sections, you only set the variables for the last 3 months.  You don't set the *_five_months_ago, *_four_months_ago or *_three_months_ago variables.  You need to set those too, like you did in the first section.  mlmcc added them in his post.

You need to change

else  // current and previous month are more than 2 months apart

to

else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 3 then

(as in mlmcc's post).

And then you need to add sections for when the current and previous dates are 4, 5, or 6 or more months apart.

else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 4 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_this_month;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_this_month;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 5 then
(
days_five_months_ago := days_this_month;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_this_month;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else  // current and previous month are 6 months apart or more
(
days_five_months_ago := 0;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := 0;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
);

last_month := {Form__Project.FinalRptDt};

(days_this_month + days_one_month_ago + days_two_months_ago + days_three_months_ago + days_four_months_ago + days_five_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago + reports_three_months_ago + reports_four_months_ago + reports_five_months_ago)

James
0
Author Commented:
Sorry to be slow on this. Here's the formula I'm now using. The report runs with no errors, but the calculation is not correct when months are missing. Here's an example:

Nov 2011
Rpt 1     27 days

Dec 2011
Rpt 1    135 days

Jan 2012
Rpt 1    88 days

Mar 2012
Rpt1    123 days
Rpt 2    94 days
Rpt3     82 days
Rpt 4    51 days
Rpt 5    212 days

Apr 2012
Rpt 1    50 days
Rpt 2    62 days

The correct 6-month average is 92.40
The formula is returning 87.67

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;
Global NumberVar days_three_months_ago;
Global NumberVar days_four_months_ago;
Global NumberVar days_five_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;
Global NumberVar reports_three_months_ago;
Global NumberVar reports_four_months_ago;
Global NumberVar reports_five_months_ago;

Global DateTimeVar last_month;

if last_month = #01/01/1900# or DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 1 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 2 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 3 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_this_month;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_this_month;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 4 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_this_month;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_this_month;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else
if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 5 then
(
days_five_months_ago := days_this_month;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := reports_this_month;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
)
else  // current and previous month are 6 months apart or more
(
days_five_months_ago := 0;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {Form__Project.FinalRptDt},  "monthly");

reports_five_months_ago := 0;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
);

last_month := {Form__Project.FinalRptDt};

(days_this_month + days_one_month_ago + days_two_months_ago + days_three_months_ago + days_four_months_ago + days_five_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago + reports_three_months_ago + reports_four_months_ago + reports_five_months_ago)
0
Commented:
Can you upload the report with saved data?

mlmcc
0
Commented:
Found it.  When months are skipped you have to "throw" away that many months so for 1 skipped month
5 months ago gets 3 months ago

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;
Global NumberVar days_three_months_ago;
Global NumberVar days_four_months_ago;
Global NumberVar days_five_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;
Global NumberVar reports_three_months_ago;
Global NumberVar reports_four_months_ago;
Global NumberVar reports_five_months_ago;
Global NumberVar monthdiff;

Global DateTimeVar last_month;
monthdiff := -1;

if last_month = #01/01/1900# or DateDiff ("m", last_month, {tblReports.dtDone}) = 1 then
(
monthdiff := 1;
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 2 then
(
monthdiff := 2;
days_five_months_ago := days_three_months_ago;
days_four_months_ago := days_two_months_ago;
days_three_months_ago := days_one_month_ago;
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_three_months_ago;
reports_four_months_ago := reports_two_months_ago;
reports_three_months_ago := reports_one_month_ago;
reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 3 then
(
monthdiff := 3;
days_five_months_ago := days_two_months_ago;
days_four_months_ago := days_one_month_ago;
days_three_months_ago := days_this_month;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_two_months_ago;
reports_four_months_ago := reports_one_month_ago;
reports_three_months_ago := reports_this_month;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 4 then
(
monthdiff := 4;
days_five_months_ago := days_one_month_ago;
days_four_months_ago := days_this_month;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_one_month_ago;
reports_four_months_ago := reports_this_month;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 5 then
(
monthdiff := 5;
days_five_months_ago := days_this_month;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_this_month;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else  // current and previous month are 6 months apart or more
(
monthdiff := 6;
days_five_months_ago := 0;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := 0;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
);

last_month := {tblReports.dtDone};

(days_this_month + days_one_month_ago + days_two_months_ago + days_three_months_ago + days_four_months_ago + days_five_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago + reports_three_months_ago + reports_four_months_ago + reports_five_months_ago);

mlmcc
0

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.

Commented:
Honestly, it took me a while to see it, but I think mlmcc is correct.  It was simpler when it was just the last 3 months.  If you skipped 1 month, you just had to move the previous month (in this_month) to "2 months ago" and set "1 month ago" to 0.  If you skipped 2 or more months, you just set the previous 2 months to 0.  But with the last 6 months, it's not as simple, and I didn't change the shifting accordingly.  For example, you would normally move *_four_months_ago to *_five_months_ago, but if you skip 1 month, you need to move *_three_months_ago to *_five_months_ago instead.  Since you skipped a month, the old *_four_months_ago is now 6 months ago, so it's outside of your "last 6 months" range.

FWIW, you can presumably leave out the monthdiff variable that he added.  Since he doesn't actually use it, I'm assuming that it was added for diagnostic purposes.

Thanks for the correction mlmcc.  I was doing the shifting correctly in my head, so I don't know how long it would have taken me to realize that the formula wasn't doing the same thing.  :-)

James
0
Commented:
It was.  I displayed it instead of the result of the summation to see if the month difference was being calculated correctly.

I added several other debugging formulas to find the issue.  From his numbers the December value was not being moved when the month was missing.

The 6 month total was 924, 10 reports thus 92.4 average
87.67 being a 2/3 value indicated to mee either the divisor was 9 or 3.  9 * 87.67 = 789  and 924 - 135 = 789

I couldn't see it in the code since it looked right.

I added a formula that showed the 6 month day totals to the group footer and realized we weren't moving the values far enough
The results were

0      0        0         0          0          27
0      0        0         0         27       135
0      0        0       27       135         88
0      0      27       88           0       562    should have been         0    27  135  88      0     562
0    27      88         0        562      112    should have been       27  135    88    0  562     112

mlmcc
0
Author Commented:
Thanks very much for all your help on this. I really appreciate it!! Just to make sure I am straight, is this the formula I should use? (I took out the monthdiff variable.) Also, since I'm new to ExpertsExchange how would I award points since both mlmcc and James contributed to the solution? Thanks again.

WhilePrintingRecords;

Global NumberVar days_this_month;
Global NumberVar days_one_month_ago;
Global NumberVar days_two_months_ago;
Global NumberVar days_three_months_ago;
Global NumberVar days_four_months_ago;
Global NumberVar days_five_months_ago;

Global NumberVar reports_this_month;
Global NumberVar reports_one_month_ago;
Global NumberVar reports_two_months_ago;
Global NumberVar reports_three_months_ago;
Global NumberVar reports_four_months_ago;
Global NumberVar reports_five_months_ago;

Global DateTimeVar last_month;

if last_month = #01/01/1900# or DateDiff ("m", last_month, {tblReports.dtDone}) = 1 then
(
days_five_months_ago := days_four_months_ago;
days_four_months_ago := days_three_months_ago;
days_three_months_ago := days_two_months_ago;
days_two_months_ago := days_one_month_ago;
days_one_month_ago := days_this_month;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_four_months_ago;
reports_four_months_ago := reports_three_months_ago;
reports_three_months_ago := reports_two_months_ago;
reports_two_months_ago := reports_one_month_ago;
reports_one_month_ago := reports_this_month;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 2 then
(
days_five_months_ago := days_three_months_ago;
days_four_months_ago := days_two_months_ago;
days_three_months_ago := days_one_month_ago;
days_two_months_ago := days_this_month;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_three_months_ago;
reports_four_months_ago := reports_two_months_ago;
reports_three_months_ago := reports_one_month_ago;
reports_two_months_ago := reports_this_month;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 3 then
(
days_five_months_ago := days_two_months_ago;
days_four_months_ago := days_one_month_ago;
days_three_months_ago := days_this_month;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_two_months_ago;
reports_four_months_ago := reports_one_month_ago;
reports_three_months_ago := reports_this_month;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 4 then
(
days_five_months_ago := days_one_month_ago;
days_four_months_ago := days_this_month;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_one_month_ago;
reports_four_months_ago := reports_this_month;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else
if DateDiff ("m", last_month, {tblReports.dtDone}) = 5 then
(
days_five_months_ago := days_this_month;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := reports_this_month;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
)
else  // current and previous month are 6 months apart or more
(
days_five_months_ago := 0;
days_four_months_ago := 0;
days_three_months_ago := 0;
days_two_months_ago := 0;
days_one_month_ago := 0;
days_this_month := Sum ({@Cycle Time}, {tblReports.dtDone},  "monthly");

reports_five_months_ago := 0;
reports_four_months_ago := 0;
reports_three_months_ago := 0;
reports_two_months_ago := 0 ;
reports_one_month_ago := 0;
reports_this_month := DistinctCount ({tblReports.projectNum}, {tblReports.dtDone},  "monthly")
);

last_month := {tblReports.dtDone};

(days_this_month + days_one_month_ago + days_two_months_ago + days_three_months_ago + days_four_months_ago + days_five_months_ago) /
(reports_this_month + reports_one_month_ago + reports_two_months_ago + reports_three_months_ago + reports_four_months_ago + reports_five_months_ago);
0
Commented:
That looks correct.

WHen you go to close choose the comment you want as the answer by clicking the ACCEPT MULTIPLE SOLUTIONS link.  Then select the other comments as you see fit.

mlmcc
0
Commented:
FWIW, that formula looks OK to me too.

If you were asking how many points to give to each of us, I'd suggest that you give most of them to mlmcc, because my posts were mostly expanding on what he had posted (explaining some details and that kind of thing).

James
0
Author Commented:
That worked perfectly! Thanks again for all your help.
0
Author Commented:
OK, I know you have probably heard enough about this report, but I am having one remaining problem. I need to calculate and print the 6-month moving average even in months where no reports were issued. I posted a new question:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27679905.html
0
###### 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.