How to summarize min & max for groups 1

[merenakk]

Please help,

deal types grouped within porfolios. Max found for each deal types, how to sum a max for a porfolio?

Book1

Loan 01 55
Loan 02 555

Max 555

Deposit 01 66
Deposit 02 666

Max 666

Sum of Max for porfolio Book1 :

Thank you

 

[Mountaine]

If I understand, you're using a "summary" to get the max for each deal type. If that's true, simplest way is to copy the "max" object from the GF2 (grouping by deal type) to the GF1 (grouping by portfolio). If this doesn't make sense, provide more detail on how you're getting the max for each deal type.

 

[Madawc]

If you group by 'porfolio Book1', then you can get summary totals for the group, including minimum and maximum.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Madawc Williams (East Anglia, UK)

 

[merenakk]

The maximum has been found for each deal type within Porfolio.

Porfolio 1

Loan 1 55
Loan 2 555
Max 555

Dep 1 66
Dep 2 666
Max 666

Sum of Max = Max Loans + Max Deposits for Porfolio 1 = 666+555 (that's the formula I'm looking for)

Same thing for Porfolio 2 etc.
Thanks again

 

[Madawc]

If it's only some records within the group, then you need a running total. This can 'evaluate' using a formula, and you can set it to add just for loans and deposits.

Madawc Williams (East Anglia, UK)

 

[merenakk]

Thank you, but what type of formula would it be, please help,

Thanks

 

[Madawc]

That depends on your data. If I take it literally, it might be {your.type} = "Dep" or {your.type} = "Loan"

Madawc Williams (East Anglia, UK)

 

[lbass]

You need to use a variable. Create three formulas:

//{@reset} to be placed in the portfolio group header:
whileprintingrecords;
numbervar summax;
if not inrepeatedgroupheader then
summax := 0;

//{@accum} to be placed in the {table.dealtype} group footer:
whileprintingrecords;
numbervar summax := summax + maximum({table.amt},{table.dealtype});

//{@display} to be placed in the portfolio group footer:
whileprintingrecords;
numbervar summax;

-LB

 

[merenakk]

Thank you all,

Especially lbass! Your formula solved the problem.

 

[madelca100]

lbass,

I tried a similar calculation for Grand Totals but, under certain circumstances, would come up with a number that was too high. I finally figured out that some 'adjusters' in my lookup table were returning Nulls.

btw - the Null showed up as 0 in the report

If I didn't test for Null the system would add the number just prior to the null again.

So: 10 + 20 + null + 30
became 10 + 20 + 20 + 30

which gave me 80 instead of 60

(File/Option/Reporting - "Convert database Null values to default" didn't make a difference.)

The fix:
numbervar BeginGT := BeginGT +
iif(isnull({claim_count_by_adjuster.Begin_Open}),0,
Maximum ({claim_count_by_adjuster.Begin_Open}, {claim_count_by_adjuster.Full Name}));

mike

 

[lbass]

Mike, I probably would have started out by using a formula:

if isnull({claim_count_by_adjuster.Begin_Open}) then 0 else
{claim_count_by_adjuster.Begin_Open}

...and then inserted a maximum on that formula. Even so, I think the reset formula should have taken care of any nulls, returning a zero, not the previous value. Not sure why that didn't work.

-LB