Crystal Reports 6 mo. Moving Average - Need to print average in months where there is no data

bms22
bms22 used Ask the Experts™
on
I am using this formula to calculate a 6 month moving average. As you can see from the attachment, the moving average only prints in months where there is data. I need to calculate and print the 6 month moving average in all months, even months where there is no data. For example, on the attachment, there is no data for Nov 2010. I still need to calculate and print the 6-month moving average for November.

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_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}, {Form__Project.FinalRptDt},  "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 ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
  )
else
  if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 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}, {Form__Project.FinalRptDt},  "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 ({Form__Project.Project}, {Form__Project.FinalRptDt},  "monthly")
  )
else
  if DateDiff ("m", last_month, {Form__Project.FinalRptDt}) = 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}, {Form__Project.FinalRptDt},  "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 ({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);
Rpt.pdf
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I know you have been working on this for a while.

What is the data source?
Tables, views, SP, Command?

Can you add a table that has 1 column a date.  Records will be 1 month year for each mnth and year you need or may need in the future say through 2015 or 2020

If so you could use that table as the master and left join you table to it.  In that way every month would have data

There is another way using some tricks in Crystal.
I'll see what I can do.  Will you ever run a range where the 1st 6 months are empty?

mlmcc
mlmcc has pretty much covered it, but FWIW ...

 In most cases, the simplest thing, as far as the report goes, would probably be if you could add "dummy" records for the missing months.  Like using a table that has a record for every month, as mlmcc described.  However, in your case I think there is a little complication, since you're doing calculations that use the record count, which would include those extra records.  I think that could be handled pretty easily.  It's just a detail that would need to be addressed.

 Otherwise you have to add something to the report that will recognize when some months have been skipped and generate whatever output you want to see (Month name and an average?) for those months.

 James

Author

Commented:
Thanks. The data source is a Lotus Notes database. I pull fields from a form in the database.  No, the first six month would never be empty. As I'm sure you have figured out by now, I am a real Crystal Reports newbie, would need some remedial instructions on how to proceed.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The questions you have been asking on this are difficult even for experienced CR users.  I spent a lot of time working up a solution to show data when none existed.  Unfortunately I forgot to keep acopy of the report I gave to the client.  I was working on their systems and I didn't think to email it to myself.

I remember how I did it, so I can try to recreate.

Are you passing a date range to the report or a start date?

mlmcc
bms22,

 Is there a maximum number of consecutive months that might be missing, like no more than 5 or 10?  If you're giving the report an ending date, then you could create an unlimited number of "missing" months by using an ending date in the future (eg. 12/31/2013).  All the months in the future would presumably be "missing" (have no records).  Assuming that that's not an issue (you won't be using future dates, or don't really consider them to be "missing"), is there a limit on how many months in a row, in the past, might not have any data?


 mlmcc,

 Assuming that there is a fairly small limit on the number of consecutive missing months, what would you think about using a formula similar to the old 6 month formula, but it only shifts one month at a time and changes the last_month variable accordingly.  Say there would never be more than 5 consecutive missing months.  Create 5 group footer (or wherever the average is calculated) sections before the one with the old 6 month formula and put this new formula in each section.  Suppress the sections based on the month gap.  If the difference is more than 1 month, the new formula shifts the monthly variables 1 month, calculates the average, and adds 1 month to last_month (which could be used to produce the month in the footer, along with the average).  Then it does the same thing in the next section, and so on, until the gap is down to 1 month.  Then you'd hit the old formula.  This new formula could possibly even replace the old formula.  I'm not entirely sure about that.

 Just wanted to see what you thought before I put too much time into it.  And I need to find out how many consecutive missing months there could be.  I was thinking of a separate section for each missing month, so if that number could get too high, it might not be practical.

 James

Author

Commented:
Thanks for your help. The only date values that pass to the report are the specific dates you see on the sample report -- the "Actual Start Date" and "Final Report Issue Date." Those are just specific dates. The "Cycle Time" is calculated by subtracting the two dates. The other fields on the report are simply pulled in from the Notes database. I think it would be reasonable to assume there would never be more than five consecutive months of missing data.
OK, my basic idea seems to work, although it's not quite as "simple" as I thought.  I'm assuming that you have the 6 month average formula in a group footer section for the FinalRptDt group.

 My original idea was to add more group footer sections (one for each potential missing month) above the group footer section that the old average formula is in, and put a new formula in each of those sections.  The idea was that the new formula would check to see if the difference between the current month and last_month was more than 1 month.  If so, it would bump all of the variables, including last_month, by 1 month, and calculate the average.  Otherwise, it would not do anything and that group footer section would, in some way, be suppressed.  So, if you thought there could be up to 5 missing months, you would create 5 new group footer sections and put a copy of that formula in each section.

 But that doesn't quite work.  CR only seems to evaluate a formula once per "section", so if you create 5 new group footer sections and put a copy of the same formula in each one - Or, for that matter, put 5 copies of the same formula in one section - CR only evaluates the formula once, so the variables only get changed one time.  I'm not sure if that's something that I've just never realized before, or something that I knew and just forgot.  :-)

 IAC, one workaround is to create 5 new groups on the same date field, also grouped by month.  Then you can put a copy of the formula in each separate group footer.  I tried that and it seemed to work fine.  Having 5 groups on the same field seems a bit "awkward", for lack or a better term, but I can't think of any real problems with it.  It might depend on the rest of your report.  If you want to be able to tell the groups apart, you can create 5 formulas that just include your date field, and create the new groups on those formulas instead.

 Another option, which just occurred to me as I was writing this post, would be to create 5 copies of the formula.  I haven't tested that, but it should work.  Creating the formulas would be easy.  Just create the first formula and then copy&paste into the other formulas.  The downside is that then you've got 5 copies of the formula to maintain.  The upside is that you wouldn't need to add 5 new groups.  You could just create the new group footer sections for the existing group and put a different formula in each section.

 Your call.  I'm kind of torn.  I like having just one copy of the new formula, but having to create 5 new groups bothers me.  <shrug>

 Anyway, the new formula is below.  You would put that in the new group footer sections, as described above.  The old average formula would be in the last group footer section.

 FWIW, it might be possible to replace the old formula (in the last footer) with the new one, but if you keep the old one at the end, then if you ever have more missing months than you have formulas, the old formula will skip the extra months and show you the correct current month.  The new formula would just add 1 to the last month and you wouldn't see the current month on the report.  (I hope that made sense :-)


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;

Local NumberVar avg;

if last_month <> #01/01/1900# and 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 := 0;

  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 := 0;

  last_month := DateAdd ("m", 1, last_month);

  if reports_this_month + reports_one_month_ago + reports_two_months_ago +
   reports_three_months_ago + reports_four_months_ago + reports_five_months_ago <> 0 then
    avg :=
    (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);

  CStr (last_month, "MMMM, yyyy") + "   " + CStr (avg, "#.00")
);

Open in new window



 Before calculating the average, I check to make sure that the total report count for the last 6 months is not 0, to make sure there isn't a "divide by 0" error.  I'm not sure if that would be possible as long as you're looking at the last 6 months and only allowing for a maximum of 5 missing months.  Just playing it safe.

 You need to somehow suppress these new sections except when you need them for the missing months.  The simplest thing is to have that formula produce all of the output for that month (including the month name, etc.), and have the formula just produce nothing when there is no missing month, and then suppress the section when it's blank.  That's what I did in that formula.  I put the average in a variable (avg), and then use CStr to produce the month name and the year, and the average (formatted with 2 decimal places).  Hopefully you can change that so that it matches the output on your regular month average lines.  If necessary, we can look for another way to suppress those sections when you don't need them, so that the new formula can just produce a number, like the old formula does.

 James

Author

Commented:
Thanks. I'm at a conference this week so I'll give it a try when I'm back in the office.

Author

Commented:
Sorry, this is a bit much for my simple mind. I think I'm going to try the option of creating five new group footer sections for the existing group and then put a different formula in each section. I'm a little confused on how I would modify the formula to insert into each of the five new group footer sections.
When you say "modify the formula", I'm not sure if you're talking about the old formula, or the one from my last post.  You should be using the one from my last post, and you don't actually need to modify anything.  Create 5 new formulas (eg. @missing_month_avg1, @missing_month_avg2, etc.) and just copy the formula from my last post and paste it into each of the new formulas.  You don't need to change anything in the copies, because, basically, what it does is bump the month up by 1 and calculate an average if a month is missing.  It does that each time the formula is evaluated.

 You need 5 copies of the formula because if you create 5 new group footer sections and just put a copy of the same formula in each section, it only gets evaluated once, for the group footer as a whole.  So, instead, you're creating 5 separate formulas that do exactly the same thing, and putting one formula in each section.

 As I mentioned before, I haven't actually tried this separate formula approach, but I think it will work.

 FWIW, this will work for any number of months (within reason).  5 months was just the limit that you mentioned.  If you want to allow for more, or less, missing months, just change the number of group footer sections (one for each potential missing month) and put a separate formula in each section, all of which are just copies of the formula in my last post.

 James

Author

Commented:
This definitely works! I went with the option to create five new group footer sections and copy the formula into each. I then suppressed the blank sections. Thanks very much!
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial