Solved

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

Posted on 2012-04-10
942 Views
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)
0
Question by:bms22
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 4
• 3

LVL 100

Expert Comment

ID: 37829045
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

LVL 35

Expert Comment

ID: 37831241
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 Comment

ID: 37840249
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

LVL 100

Expert Comment

ID: 37840911
Can you upload the report with saved data?

mlmcc
0

LVL 100

Accepted Solution

mlmcc earned 150 total points
ID: 37841159
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

LVL 35

Assisted Solution

James0628 earned 50 total points
ID: 37841401
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

LVL 100

Expert Comment

ID: 37843475
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 Comment

ID: 37853608
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

LVL 100

Expert Comment

ID: 37853774
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

LVL 35

Expert Comment

ID: 37854578
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 Closing Comment

ID: 37856771
That worked perfectly! Thanks again for all your help.
0

Author Comment

ID: 37857184
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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â€¦
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This videoâ€¦
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
###### Suggested Courses
Course of the Month7 days, 1 hour left to enroll