Solved

Need to calculate 6-month moving average in Crystal Reports

Posted on 2012-04-10
12
909 Views
Last Modified: 2012-04-17
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.

In header:

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
Comment
Question by:bms22
  • 5
  • 4
  • 3
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
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 34

Expert Comment

by:James0628
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

by:bms22
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

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

mlmcc
0
 
LVL 100

Accepted Solution

by:
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 34

Assisted Solution

by:James0628
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 100

Expert Comment

by:mlmcc
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

by:bms22
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

by:mlmcc
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 34

Expert Comment

by:James0628
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

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

Author Comment

by:bms22
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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 video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now