min,max,avg of sum 2

[mberni]

Hi!

I'd like to compute the minimum (maximum and average also) for a groupsum.

Something like that:

header1
Detail: field1
Footer2 sum of field1
footer1 min(sum of field1)
max(sum of field1)
avg(sum of field1)

The only way i imagine is to write a formula in which i store the current minimum and update it, when a lesser value appears.

I wonder if there is a shortcut for that.

 

[lbass]

I don't think so. Do you need help with using variables to find these values?

-LB

 

[mberni]

thanks lb,
first i will try on my own.

maybe i'll remember your offer and return later )

 

[mberni]

ok ... next question

i wrote a formula to declare my max, min and avg-variables and placed it in the GH1.

now i'd like to write formula for my GF2 to do something like that:

shared l_mymax as number
shared l_mymin as number
shared l_myavg as number

if {#srbet} > l_mymax then LET l_mymax=[red]{#srbet}[/red]
if {#srbet} < l_mymin then LET l_mymin=[red]{#srbet}[/red]
formula = 0

but ...

crystal tells me that i must use a number instead of [red]{#srbet}[/red]

so i am not allowed to address sum-fields within that context?

Please don't tell me i must use a formula for the sum itself too? ...

 

[lbass]

I think your running total result might be a currency. The following is in Crystal syntax. Also note that this should be placed in the group footer, not the group header.

shared currencyvar mymax;
shared currencyvar mymin;
shared currencyvar myavg;
shared numbervar cnt := cnt + 1;

if {#srbet} > mymax then
mymax := {#srbet};
if groupnumber = 1 or
{#srbet} < mymin then
mymin:={#srbet};
myavg := myavg + {#srbet};

For the display formulas in the report footer, you would use:

//{@displmax}:
whileprintingrecords;
shared currencyvar mymax;

//{@displmin}:
whileprintingrecords;
shared currencyvar mymin;

//{@displave}:
whileprintingrecords;
shared currencyvar myavg;
shared numbervar cnt;
myavg/cnt;

-LB

 

[mberni]

thanks lb, but the results are a bit suspicious ...

max and avg are fine.

In the first GF1 the mymin is correct but in all following GF1-Footers mymin is 0.

I have placed the display-formulas in GF1 and the computing-formula in GF2. Furthermore i have placed in GH1 :

//{@myinit}:
shared currencyvar mymax:=0;
shared currencyvar mymin:=0;
shared currencyvar myavg:=0;
shared numbervar cnt:=0;

While debugging i found out the following:

when i run my report against the complete data, the GF1 for group 90000 looks like that (there are eight more groups before 90000)

Group Min AVG Max
90000 0,00 10.607,14 35.007,95

when i run my report only for group 90000 the result looks like that:

Group Min AVG Max
90000 24,09 10.607,14 35.007,95

and thats correct, since 24,09 is the min-value of that group, while 0 does not appear in group 90000.

whats going wrong here?

 

[lbass]

I didn't know you had more than one group from your first post. Change the formula to:

shared currencyvar mymax;
shared currencyvar mymin;
shared currencyvar myavg;
shared numbervar cnt := cnt + 1;

if {#srbet} > mymax then
mymax := {#srbet};
if cnt = 1 or
{#srbet} < mymin then
mymin:={#srbet};
myavg := myavg + {#srbet};

Also, I didn't mention this earlier, but these variables don't need to "shared" unless you are planning on passing them to or from a subreport. Doesn't hurt to leave them as is though.

-LB

 

[paulmarr]

The reason this formula works is because the "shared" scope vaiable is the equivalent of using "Whileprintingrecords".
In the example:

"For the display formulas in the report footer, you would use:

//{@displmax}:
whileprintingrecords;
shared currencyvar mymax;"

As long as the vaiable is shared the whileprintingrecords is not needed - but if the variable was local or global then you would need it.
Shared variables force the processing until the last pass (as well as allowing subreports to access)

Cheers
paulmarr

 

[mberni]

Thank you lb and pm for your help, everything is fine now.

Only one more thing i noticed, which doesn't matter in my context but makes me curious.

while debugging i displayed GF2-Data, i.e. the sum-lines of which i compute the min, max and avg values. To check for the min and max values i let crystal sort them ascending.

Now it seems that crystal has some problems to sort negative values, as i get the following:

-135,17
-196,42
-223,47 (the minimum)
-222,70
-214,74
and so on up to the maxixum

but -223,47 is obviously < -135,17, same goes for -196,42

so why are they listed before my minimum?

the displayed value is a running total of a currency field.

positive values are sorted correctly.

 

[paulmarr]

If you are showing a running total - then that is what it is doing - adding positive and negative values as it goes.

What you want to do is a TopN/BottomN Sort using the Group Sort Expert - where you can choose to sort "ALL" based on what ever formula or formulas you want - so you can sort on Max/Min and Average together.

I hope this helps

Cheers
paulmarr

 

[mberni]

thanks for your feedback paulmarr,

maybe i explained not clearly enough, but the values i mentioned are sums based on grouplevel 2 and i already did what you suggest and told the group sort-expert to sort the group2-lvl based on the sum, so i would expect them to appear in ascending order.

This works fine, as long as the values are positive, these are ascending. But when a group contains negative sums they are mixed up within the negative values.

 

[lbass]

Paul,
I stand corrected, although I'm not sure why you wouldn't just use whileprintingrecords (because "shared" is shorter?), and reserve "shared" for its more common usage with passing values among subreports and the main report.

mberni,
You were using a running total for the group #2 "sum". That implied to me that you were unable to use an inserted summary, so I'm wondering how you were able to use a group sort--which requires an inserted summary. I just tested the group sort with negative values and it sorted correctly.

-LB

 

[mberni]

lbass,
you are right, the groupsort uses an inserted summary, which i first used in my report too, but when i hit the min-problem i created a running total to be able to address the sum in my formula. so i displayed the running total while i sorted using the inserted summary. i changed that for a try now (display of inserted summary now) and now the sort makes sense.

many thanks for your advise !

the sealed book of crystal reports opened a bit more

 

[jjappinga]

I have a particular problem trying to average a maximum(field). I can list the maximum but when ever I try to average only the maximum of the field, it uses both the lower and higher figures in the average, thus the average is always off. This has to do with NetG courses, where the if the pretest is a 92 and the posttest is an 88, the pretest score is accepted; but again, it takes both scores into the entire average for all the subcourses. Any suggestions?

 

[lbass]

Let's say you have a group on {table.student}. You could create formulas like this:

//{@accum} to be placed in the student group header or footer:
whileprintingrecords;
numbervar maxscore := maximum({table.score},{table.student});
numbervar summax := summax + maxscore;
numbervar cnt := cnt + 1;

//{@ave} to be placed in the report footer:
whileprintingrecords;
numbervar summax;
numbervar cnt;
summax/cnt

-LB