Manual Crosstabs Averages and Totals by Month 1

[bmacdo]

Using Crystal 10 ...

I've got a formula to average the daily population at our facilities by month. I want to print all months daily average throughout a year in rows and then total the averages of our facilities within facility type. I believe I can't use crosstab to average and total simultaneously.

The report should look ~like this:

7/07 8/07 9/07
fac1 10 20 15 (daily average by month)
fac2 20 40 35
-- -- --
type1 30 60 50 (totals of monthly daily avg)

fac3 ... etc.

Am grouping by Facility Type, Facility, Year, Month, Day.

When I attempt to store the value of a particular month's average to print in the group footer & the evaluation for a particular month is false, the result resets zero. I have used formulas that bump counters conditionally many times, but have lost it on this one as I am averaging the counters only once per month/facility.

Here's a sample formula that is working in the respective month, but resetting to zero otherwise:

WhilePrintingRecords;
numbervar MnthlyDailyAvg;

If {@MonthNum} = 7 then MnthlyDailyAvg := {#Monthly Totals}/{#DaysInMnthNum}

Thanks for any and all help...

Brad Macdonald

 

[bmacdo]

The latter.

month ... 4/07 5/07 6/07
---- ---- ----
fac1 20 25 15
fac2 15 20 20
---- ---- ----
facType1 35 45 55

Happens for each facility type on only that last month whatever the month parameter value is input.

Thanks,
Brad

 

[bmacdo]

p.s. was wondering if my accumulating formulas might be placed incorrectly in the report but I don't think so ...

The report has four groups: facility type, facility (name), year, month num, and date. The facility monthly averaging formulas and facility type monthly averages totaling formulas are in the gfs for month, a and b gf respectively ...

Brad

 

[lbass]

The placement of your formulas is incorrect, I think, and in your post of 11:55, you are overcomplicating things.

Just use one formula in the month group section for each month:

WhilePrintingRecords;
numbervar JuneMnthlyAvg;
numbervar JuneAvgPgmTotal;
If {@MonthNum} = 6 then
JuneMnthlyAvg := {#Monthly Totals}/{#DaysInMnthNum} else
JuneMnthlyAvg := JuneMnthlyAvg;
JuneAvgPgmTotal := JuneAvgPgmTotal + JuneMnthlyAvg;
JuneMnthlyAvg;

In the Facility Name group footer, use:

WhilePrintingRecords;
numbervar JuneMnthlyAvg;

Then in the Facility Type Footer, add:

WhilePrintingRecords;
numbervar JuneAvgPgmTotal;

You should have a reset formula for JuneMnthlyAvg in the Facility Name header, and a reset for JuneAvgPgmTotal in the Facility Type header.

Making this change might clear up the problem. In the future please identify your sample data by the group sections which they represent and also identify the group sections in which formulas are placed.

-LB

 

[bmacdo]

Will do. Thanks for all your time.

Brad