asked on

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

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

Mike McCracken

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

James0628

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

bms22

ASKER

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.

Mike McCracken

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

James0628

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

bms22

ASKER

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.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bms22

ASKER

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

bms22

ASKER

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.

James0628

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

bms22

ASKER

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!

James0628

You're welcome. Glad I could help.

James