Using Sum() in formula

[simonfisher]

I need to determine the Sum of groups of records and for each group use the Sum to determine the Average for the group.

My data is grouped on PClass and is as follows :

PClass Months No. (Month * No.)

MONEY MARKET 184 1 184
MONEY MARKET 52 2 104
MONEY MARKET 34 6 204
MONEY MARKET 28 1 28
MONEY MARKET 80 3 240
MONEY MARKET 22 1 22
MONEY MARKET 22 0 0
MONEY MARKET 36 2 72
MONEY MARKET 36 4 144
MONEY MARKET 32 2 64
MONEY MARKET 40 1 40
MONEY MARKET 20 1 20
MONEY MARKET 10 1 10
MONEY MARKET 37 14 518
Sum: 633 39 1650 42 (1650 / 39)

CASH MAX 133 29 3857
CASH MAX 93 19 1767
CASH MAX 110 1 110
CASH MAX 160 5 800
CASH MAX 105 6 630
CASH MAX 124 30 3720
CASH MAX 132 4 528
CASH MAX 145 3 435
CASH MAX 135 17 2295
CASH MAX 156 9 1404
CASH MAX 98 9 882
CASH MAX 154 6 924
Sum: 1545 138 17352 126 (17352 / 138)

The data is summarised and only the numbers 42 and 126 are printed out for each group alongwith PClass.

How do I calculate the Sum of the No. column in order to use it in my calculations?

Any help would be greatly appreciated.

Thanx

Simon Fisher

 

[paulmarr]

Use the Sum function with (2) arguments:

Sum({NUMBERCOLUMN_FIELD},{FIELD YOU HAVE GROUPED BY}

Cheers
paulmarr

 

[simonfisher]

My data is actually grouped on two fields with PClass being the lowest level. The output should look like this:

PROD_CLASS Ave Age Jul Ave Age Aug Ave Age Sep
------------ ----------- ----------- -----------
Product One 34 37 40
Product Two 88 91 94
Product Three 90 93 97
Product Four 62 65 69
Product Five 211 215 219

The output is displayed in Group Footer #2. I use formulae to determine in which column the result should go.
In order to arrive at each value I need to use this formula:
SUM(Average No. of Months * No. of Active Accounts) / SUM(No. of Active Accounts). This gives me the Ave Age for each product in each month. I am able to obtain the (Average No. of Months * No. of Active Accounts) as they are calculated from the table. I then insert a Summary field in the footer. But when I divide the result in the formula by SUM(No. of Active Accounts) I get the Error 'A Summary has been specified on a non-recurring field'.

The formula for each field is as follows:

if {TEMP_FLUM_HIST.MONTH} = "Jul"
then
if {TEMP_FLUM_HIST.LABEL} = "Prev"
then
({TEMP_FLUM_HIST.PREV_AVE_AGE_MTHS} * {TEMP_FLUM_HIST.PREV_ACTIVE_CNT}) / Sum ({TEMP_FLUM_HIST.PREV_ACTIVE_CNT},{TEMP_FLUM_HIST.PROD_CLASS} )
else
({TEMP_FLUM_HIST.CURR_AVE_AGE_MTHS} * {TEMP_FLUM_HIST.CURR_ACTIVE_CNT}) / Sum ({TEMP_FLUM_HIST.CURR_ACTIVE_CNT},{TEMP_FLUM_HIST.PROD_CLASS} );

 

[Madawc]

Try a running total. This will sum according to a formula, which can be quite a compelex set of tests.

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.

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)