brokeMyLegBiking
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.
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.
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})
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})
Should have been:
{table.field1} + previous({table.field1})
{table.field1} + previous({table.field1})
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})
When I use the formula: (VB syntax) it says "this field has no previous or next value.
previousValue({@YtdAve})
Can you post your rowSum formula?
ASKER
The "rowSum" formula is called "YtdAve", here it is:
----------------
WhilePrintingRecords
Dim totalmonths as Number
totalmonths = IIF(isnull({pri_pmpm_byspe cialty.01} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.02} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.03} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.04} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.05} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.06} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.07} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.08} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.09} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.10} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.11} ),0,1) + _
IIF(isnull({pri_pmpm_byspe cialty.12} ),0,1)
Dim s as Double
s = 0
if(not isnull({pri_pmpm_byspecial ty.01})) then s = s + {pri_pmpm_byspecialty.01}
if(not isnull({pri_pmpm_byspecial ty.02})) then s = s + {pri_pmpm_byspecialty.02}
if(not isnull({pri_pmpm_byspecial ty.03})) then s = s + {pri_pmpm_byspecialty.03}
if(not isnull({pri_pmpm_byspecial ty.04})) then s = s + {pri_pmpm_byspecialty.04}
if(not isnull({pri_pmpm_byspecial ty.05})) then s = s + {pri_pmpm_byspecialty.05}
if(not isnull({pri_pmpm_byspecial ty.06})) then s = s + {pri_pmpm_byspecialty.06}
if(not isnull({pri_pmpm_byspecial ty.07})) then s = s + {pri_pmpm_byspecialty.07}
if(not isnull({pri_pmpm_byspecial ty.08})) then s = s + {pri_pmpm_byspecialty.08}
if(not isnull({pri_pmpm_byspecial ty.09})) then s = s + {pri_pmpm_byspecialty.09}
if(not isnull({pri_pmpm_byspecial ty.10})) then s = s + {pri_pmpm_byspecialty.10}
if(not isnull({pri_pmpm_byspecial ty.11})) then s = s + {pri_pmpm_byspecialty.11}
if(not isnull({pri_pmpm_byspecial ty.12})) then s = s + {pri_pmpm_byspecialty.12}
IF totalmonths > 0 THEN
formula = s / totalmonths
ELSE
formula = 0
END IF
----------------
WhilePrintingRecords
Dim totalmonths as Number
totalmonths = IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
Dim s as Double
s = 0
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
if(not isnull({pri_pmpm_byspecial
IF totalmonths > 0 THEN
formula = s / totalmonths
ELSE
formula = 0
END IF
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_by specialty. CenterName }) = {pri_pmpm_byspecialty.Cent erName} then
formula = {@YtdAve} + previousValue({@YtdAve})
else
formula = ""
end if
if previousValue({pri_pmpm_by
formula = {@YtdAve} + previousValue({@YtdAve})
else
formula = ""
end if
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With the above, I mean 'WhilePrintingRecords' in your YtdAve function.
After that, you should be able to create a Σ summary for @YtdAve.
--
Outin
After that, you should be able to create a Σ summary for @YtdAve.
--
Outin
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_byspe cialty.01} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.02} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.03} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.04} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.05} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.06} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.07} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.08} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.09} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.10} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.11} ),0,1) +
IIF(isnull({pri_pmpm_byspe cialty.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_byspeci alty.Cente rName}) = {pri_pmpm_byspecialty.Cent erName} then
{@YtdAve} + Previous({@YtdAve})
else
0
Hope this helps
Gary
@CountMonths
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
IIF(isnull({pri_pmpm_byspe
@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_byspeci
{@YtdAve} + Previous({@YtdAve})
else
0
Hope this helps
Gary
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
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
ASKER
that worked, thanks!
Glad I could be of assistance...
Outin
Outin
ASKER
3
4
5 formula= 9
Formula = (current record) + (previous record._