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

 

[lbass]

It is unclear what the issue is.

-LB

 

[bmacdo]

Sorry ...

instead of getting

7/07 8/07 9/07
fac1 10 20 15

in the facilities gf I am getting

7/07 8/07 9/07
fac1 0 0 15

when the last month of the range, e.g., is Sept.

When I put the month formulas in the month gf, I can see they count for the month they are in, but when the month changes the formula result resets to zero.

Brad

 

[lbass]

Please explain how you have the running totals {#Monthly Totals} and {#DaysInMnthNum} set up.

-LB

 

[bmacdo]

#Monthly Totals counts client_id for each record and resets on change of group formula @month_num.

#DaysInMnthNum does a distinct count on calendar date for each record and also resets on change of group formula @month_num.

@month_num is just "Month({foo.Calendar.TheDate})"

The report is grouped by facility type, facility, year, month_num, date. The year is in there as the report will be run on a fiscal year basis starting in July. At present the report just has begin and end parameter dates.

The detail record is client_id in one of our facilities per date.

Thank you for looking at this ...

Brad

 

[lbass]

Try this. In the month group footer, you should use:

WhilePrintingRecords;
numbervar MnthlyDailyAvg;

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

Then in the facility group footer, use a display formula:

WhilePrintingRecords;
numbervar MnthlyDailyAvg;

-LB

 

[bmacdo]

That worked.

I could have sworn I tried that earlier and got a message to the effect that a formula could not refer directly or indirectly to itself, but obviously the formula was messed up in another way then.

Thanks very much!

Brad

 

[bmacdo]

However, I am not allowed to sum the monthly averages by inserting a summary, summarizing manually, or attempting a running total?

Brad

 

[bmacdo]

Please disregard most recent post ... I figured it out.

Brad

 

[bmacdo]

I hope I can retrieve someone helpful to this cold thread.

I constructed the monthly averages and displays of it as recommended and just noticed that in the final month of the date range, no matter what the month ending is in the range, the final facility total within that facility type is counted twice, e.g.

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

This is consistent in the facility type gf's.

Thanks for any and all help.

Brad

 

[lbass]

It looks like you are using the accumulation formula in the facility group footer instead of the display formula--which would just declare the variable, as in:

WhilePrintingRecords;
numbervar MnthlyDailyAvg;

-LB

 

[bmacdo]

I wish that were the case, but I am using the display formula in that footer, e.g.
----------------
WhilePrintingRecords;
numbervar DispJuneAvgPgmTot;
{@JuneAvgPgmTotal};
----------------

Brad

 

[lbass]

I don't know what's in your {@JuneAvgPgmTotal}, but I don't think it belongs there. Try just:

WhilePrintingRecords;
numbervar DispJuneAvgPgmTot;

-LB

 

[bmacdo]

I don't have it here at home but believe {@JuneAvgPgmTotal} is

WhilePrintingRecords;
numbervar JuneAvgPgmTotal;

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

It was also found, eep, that if there is a facility with no counts for the month, i.e. the facility closed during the fiscal year, the total for the facility for that month and months following prints the total for the previous facility although the total was set to reset on change of group.

Thanks very much for your help. You're really doing a great service for total strangers and this one is grateful.

Brad

 

[bmacdo]

This was the typical formula for a given month, e.g. June:

WhilePrintingRecords;
numbervar DispJuneAvgPgmTot;
{@JuneAvgPgmTotal};
--
This is {@JuneAvgPgmTotal}:

WhilePrintingRecords;
numbervar JuneAvgPgmTotal;
If {@MonthNum} = 6 then
JuneAvgPgmTotal := JuneAvgPgmTotal + {@JuneMnthlyAvg} else
JuneAvgPgmTotal := JuneAvgPgmTotal
--

I'm unclear how to get the monthly average program total into the display field of a particular month if I don't have as I currently do.

Brad

 

[bmacdo]

My other problem as indicated above is that if a facility closed within the date range of the report, totals from the previous facility are printed on that facilities line where should be zeros e.g. if a fac2 closed in July:

7/07 8/07 9/07
---- ---- ----
fac1 15 16 15
fac2 34 16 15 when it should be

7/07 8/07 9/07
---- ---- ----
fac1 15 16 15
fac2 34 0 0


The monthly average daily totals are monthly formulas created from running totals in a specified month as ({#Monthly Totals}/{#DaysInMnthNum}) and those RTs indicated to reset on change of facility. I suppose I should create manual counts by month and take out the Running Totals?

Brad

 

[bmacdo]

Fixed the problem immediately above with resets on the monthly facility totals, but am still stumped why the last facility in the last month of date range adds twice to the facility type total.

Brad

 

[lbass]

Please use my suggestion. The formulas in the group footers should ONLY reference the variable, not the formula that accumulates the values in the variable.

-LB

 

[bmacdo]

Thank you for the advice. Alas, I am still getting duplicate additions for the last facility in the last month of the date range, whatever the final month, per facility type.

My formulas read as follows for a given month, e.g. June:

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

DispJuneMnthlyAvg
-----------------
WhilePrintingRecords;
numbervar DispJuneMnthlyAvg;

JuneAvgPgmTotal
---------------
WhilePrintingRecords;
numbervar JuneAvgPgmTotal;
numbervar DispJuneAvgPgmTot;
If {@MonthNum} = 6 then
JuneAvgPgmTotal := JuneAvgPgmTotal + {@JuneMnthlyAvg} else
JuneAvgPgmTotal := JuneAvgPgmTotal;
DispJuneAvgPgmTot := JuneAvgPgmTotal;

DispJuneAvgPgmTot
-----------------
WhilePrintingRecords;
numbervar DispJuneAvgPgmTot;

Thanks again for your help,
Brad

 

[lbass]

Please clarify by showing a sample. Are you saying that the last month total is twice what it should be? Or that the last month is being counted twice in the facility total?

-LB