asked on

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.

brokeMyLegBiking

ASKER

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

3
4
5 formula= 9

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

bdreed35

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})

bdreed35

Should have been:

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

brokeMyLegBiking

ASKER

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})

bdreed35

Can you post your rowSum formula?

brokeMyLegBiking

ASKER

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

brokeMyLegBiking

ASKER

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

ASKER CERTIFIED SOLUTION

Outin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Outin

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

After that, you should be able to create a Σ summary for @YtdAve.

--
Outin

GJParker

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

Outin

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

brokeMyLegBiking

ASKER

that worked, thanks!

Outin

Glad I could be of assistance...

Outin