# summarize a formula field.

I would like to summarize a formula field by group. But when I go to add a summary field, it only allows me to summarize database fields, not forumlas.

A simple example, I wouldl ike to calculate the sum of the formula, the formula sums up the values on the row (rowSum), and the groupFormula sums up the rowSum for that group.

------group 1------
1 2 3   = 6
2 3 4   = 9
------------------
= 15
------group 2------
1 2 1   = 4
2 3 2   = 7
------------------
= 11

Does anyone know how to do this? I would be greatly appreciative of any assistance in this matter.
LVL 4
###### Who is Participating?

Commented:
Get rid of the 'WhilePrintingRecords' statement.

Imho it is not needed, en it's the reason you cannot summarize the formula.

hth,

Outin

0

Author Commented:
Is it possible to access the previous record in a formula?

3
4
5   formula= 9

Formula = (current record) + (previous record._

0

Commented:
Can you post your formula that is not letting you insert a summary on it?

I think rowSum should be something like this:

//@rowSum
{table.field1} + {table.field2} + {table.field3}

You can then insert a summary on this formula and then make a copy of the summary and paste it in the report footer to get the grand total.

There is a previous function to get the previous field value:

{table.field1} + previous{table.field1})
0

Commented:
Should have been:

{table.field1} + previous({table.field1})
0

Author Commented:
So noone knows an easy way to summarize a formula field by group?

When I use the formula: (VB syntax) it says "this field has no previous or next value.

previousValue({@YtdAve})
0

Commented:
Can you post your rowSum formula?
0

Author Commented:
The "rowSum" formula is called "YtdAve", here it is:

----------------
WhilePrintingRecords
Dim totalmonths as Number

totalmonths = IIF(isnull({pri_pmpm_byspecialty.01}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.02}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.03}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.04}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.05}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.06}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.07}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.08}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.09}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.10}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.11}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.12}),0,1)

Dim s as Double
s = 0
if(not isnull({pri_pmpm_byspecialty.01})) then s = s + {pri_pmpm_byspecialty.01}
if(not isnull({pri_pmpm_byspecialty.02})) then s = s + {pri_pmpm_byspecialty.02}
if(not isnull({pri_pmpm_byspecialty.03})) then s = s + {pri_pmpm_byspecialty.03}
if(not isnull({pri_pmpm_byspecialty.04})) then s = s + {pri_pmpm_byspecialty.04}
if(not isnull({pri_pmpm_byspecialty.05})) then s = s + {pri_pmpm_byspecialty.05}
if(not isnull({pri_pmpm_byspecialty.06})) then s = s + {pri_pmpm_byspecialty.06}
if(not isnull({pri_pmpm_byspecialty.07})) then s = s + {pri_pmpm_byspecialty.07}
if(not isnull({pri_pmpm_byspecialty.08})) then s = s + {pri_pmpm_byspecialty.08}
if(not isnull({pri_pmpm_byspecialty.09})) then s = s + {pri_pmpm_byspecialty.09}
if(not isnull({pri_pmpm_byspecialty.10})) then s = s + {pri_pmpm_byspecialty.10}
if(not isnull({pri_pmpm_byspecialty.11})) then s = s + {pri_pmpm_byspecialty.11}
if(not isnull({pri_pmpm_byspecialty.12})) then s = s + {pri_pmpm_byspecialty.12}

IF totalmonths > 0 THEN
formula = s / totalmonths
ELSE
formula = 0
END IF

0

Author Commented:
and here is my attempt (this doesn't work) at sumning the YtdAve formula (i just have to sum the previous record when the center name for the previous record is the same.

if previousValue({pri_pmpm_byspecialty.CenterName}) = {pri_pmpm_byspecialty.CenterName} then
formula = {@YtdAve} + previousValue({@YtdAve})
else
formula = ""
end if
0

Commented:
With the above, I mean 'WhilePrintingRecords' in your YtdAve function.

After that, you should be able to create a &#931; summary for @YtdAve.

--
Outin
0

Commented:
If I were you I would get rid of the variables completely and simplify these formulas (these are all Crystal syntax)

@CountMonths
IIF(isnull({pri_pmpm_byspecialty.01}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.02}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.03}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.04}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.05}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.06}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.07}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.08}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.09}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.10}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.11}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.12}),0,1)

@SumMonths
{pri_pmpm_byspecialty.01} +
{pri_pmpm_byspecialty.02} +
{pri_pmpm_byspecialty.03} +
{pri_pmpm_byspecialty.04} +
{pri_pmpm_byspecialty.05} +
{pri_pmpm_byspecialty.06} +
{pri_pmpm_byspecialty.07} +
{pri_pmpm_byspecialty.08} +
{pri_pmpm_byspecialty.09} +
{pri_pmpm_byspecialty.10} +
{pri_pmpm_byspecialty.11} +
{pri_pmpm_byspecialty.12}

@YTDAve
If {@CountMonths} <> 0 Then
{@SumMonths}/{@CountMonths}
Else
0

This should then allow you to use the Previous () function

if Previous({pri_pmpm_byspecialty.CenterName}) = {pri_pmpm_byspecialty.CenterName} then
{@YtdAve} + Previous({@YtdAve})
else
0

Hope this helps

Gary
0

Commented:
Gary,

Why use the previous function?

If the @YtdAve function is written correctly it is possible to use a normal summary which will show what's needed?

Greetz,

Outin
0

Author Commented:
that worked, thanks!
0

Commented:
Glad I could be of assistance...

Outin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.