Weighted Average of a Summary 1

[hoagieryder]

I am running into a little trouble getting the weighted average of a formula. My report is grouped by command.date.

I am placing the following formula(@SQM) in GH1.

IF (sum({@OLDBALNULL 36},{Command.date})-sum({@Quarterly Expected Payment 36},{Command.date})) = 0 then 0 else
if sum({@Gross Prepayment 36},{Command.date}) <= 0 then 0 else
sum({@Gross Prepayment 36},{Command.date})/(sum({@OLDBALNULL 36},{Command.date})-sum({@Quarterly Expected Payment 36},{Command.date}))


There are a series of nested formulas in the formula, that have formulas based on them as well. When I place that formula in GH1 the number that is returned is correct. However I want to take those values and get a weighted average with command.balace for the entire report. It will not summarize the field, what can I do about this?

Example:
command.date @SQM Sum of command.balance
GH1 9/30 1 100
GH1 6/30 2 200
GH1 3/31 3 300
RF1(not able to summ.)weightedaverage(@sqm,command.balance)

 

[Madawc]

You can't summarise a summary in Crystal, because it does the processing itself and does it in a single 'pass'.

If you did the calculation at detail-line level, you could then use Summary or Running Total to get a weaighted average for the whole report.

If you have to collect at group level and then work on that, you can do it using variables places at the relevant places.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[hoagieryder]

Unfortunately I have to do the calculation at the group level. If I were to set the @SQM to a shared variable and place it in GH1, how would I be able access these totals in the RF?

 

[lbass]

You could use variables. If I'm following, it would be:

//{@accum} to be placed in GH1:
whileprintingrecords;
numbervar sumcalc := sumcalc + ({@sqm}*{command.balance});
numbervar sumbal:= sumbal + {command.balance};

//{@result} to be placed in RF:
whileprintingrecords;
numbervar sumcalc;
numbervar sumbal;
sumcalc/sumbal

-LB

 

[hoagieryder]

Thanks LB that is what I ended up doing. Eventually I am going to have to plot @result in a chart. For instance I will take @SQM and it change it to include an IF statement, something like:
If command.rep = 1 then @SQM else 0

and then duplicate @SQM and do:
If command.rep = 2 then @SQM else 0

Then create a chart to evaluate on change of command.rep. Is this possible?

 

[lbass]

I think your initial summaries would have to be based on the conditional formulas using the rep condition. If you just reference the condition after the fact, you will just be saying:

if the current ref = 1 then show me the summary for all refs

If you want to chart on print-time formulas, you need to use a more complex approach. Please see:

http://www.sdn.sap.com/irj/boc/index?rid=/library/uuid/40e7130d-aa1e-2b10-ce9f-eee2a54ff4cd

-LB

 

[hoagieryder]

Yea,I am in the process of writing conditions for the initials summaries. Thanks for the link, I'll check it out.