| Author |
Message |
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
   Posted: 29 May 2009 at 11:40am |
Thanks, It worked.
Thanks a lot again.
|
 IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 08 Jun 2009 at 9:10am |
Hi, I installed Crystal 2008 and it's again giving me DIVISION BY ZERO error for the formula below.
ANy suggestions.
Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode})
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jun 2009 at 11:08am |
if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}))
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 09 Jun 2009 at 8:41am |
|
Thanks, this works.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jun 2009 at 8:45am |
FYI - whenever you are using division and the field you are dividing by has the potential to be Zero (not just if it is zero right now but can ever be zero) you will want to use the the "if-then" in the division formula.
if division_field = 0 then 0 else x / division_field
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 09 Jun 2009 at 10:38am |
Thanks, will keep in mind.
I have another small question. I am using this as formula in my report.
My report has a group Type(New and Renew).
Formula below is helping me to get average, but I cannot use this formula to do autosum based on Type. Any idea how can I do.
Field I am using for Type is : {UpLoad_Report_EOM;1.PolicyType}
"New" & "Renew"
"if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jun 2009 at 11:06am |
Each of these SUMS is summing at the ProgramCode group level.
Sum(x,y)
x=field to sum
y=field grouped on
If you want to do the sum at a different group level, use that group level field in the formula instead, probably will be:
if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.PolicyType}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.PolicyType})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.PolicyType})
The formula must be palced on the Group header or footer that it is evaluated for ({UpLoad_Report_EOM;1.PolicyType} group footer or header) in order to get the correct results to display.
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 09 Jun 2009 at 11:41am |
I am using 2 Group Levels
1. Policy Type (New, Renew)
2. Program code (Multiple)
Columns I am using are getting Running Totals, based on Program type.
Formula below helped me to get the right average but on this formula I cannot do autosum .
I need sum on average based on Program code first and then Policy type.
Report is showing all values correct but only SUm on AVerage is giving incorrect values..
if Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}) =0 then 0 else
(Sum({UpLoad_Report_EOM;1.PolicyPremium}, {UpLoad_Report_EOM;1.ProgramCode})
/ Sum({UpLoad_Report_EOM;1.PolicyCount},{UpLoad_Report_EOM;1.ProgramCode}))
.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jun 2009 at 11:44am |
Sorry, not sure what you mean by SUm on Average?
As far as I know Crystal does not allow the summary of a summary function.
Eg. AVERAGE of a SUM or a SUM of an AVERAGE.
|
|
IP Logged |
|
vpriyanka2001
Groupie
Joined: 28 May 2009
Online Status: Offline
Posts: 59
|
Posted: 09 Jun 2009 at 12:08pm |
Ineed Subtotal for Average in Yellow BOXES Below
The One I am showing is for New Business, same way I need for Renew Business and Finally SUM of (Ne+Renew Subtotals)
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
POLICIES ISSUED |
|
|
PREMIUM |
|
ATTRITIONS |
|
| |
|
PRGM - CODE |
|
|
|
|
|
|
|
|
|
|
|
|
AVG |
|
|
|
|
|
|
|
|
|
AVG |
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
NEW BUSINESS |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ES |
|
19 |
2 |
|
|
15 |
|
0 |
|
|
$0 |
|
0 |
0 |
|
|
1 |
|
|
5,251 |
|
5,251 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
HMS-MAIN |
|
27 |
21 |
|
|
2 |
|
7 |
|
|
$6,282 |
|
897 |
0 |
|
|
3 |
|
|
1,039 |
|
346 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
REIC/RESW |
|
22 |
0 |
|
|
0 |
|
21 |
|
|
$12,822 |
|
311 |
0 |
|
|
19 |
|
|
5,510 |
|
290 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
REXS |
|
42 |
39 |
|
|
0 |
|
33 |
|
|
$39,953 |
|
822 |
0 |
|
|
0 |
|
|
0 |
|
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
RMAX |
|
27 |
0 |
|
|
2 |
|
6 |
|
|
$84,136 |
|
7,364 |
0 |
|
|
1 |
|
|
8,441 |
|
8,441 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
RMAXF |
|
3 |
0 |
|
|
0 |
|
0 |
|
|
$84,136 |
|
0 |
0 |
|
|
0 |
|
|
0 |
|
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
STD |
|
740 |
382 |
|
|
202 |
|
111 |
|
|
$263,588 |
|
1,617 |
0 |
|
|
56 |
|
|
49,099 |
|
877 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Total New Business |
880 |
444 |
|
|
221 |
|
178 |
|
|
$263,588 |
|
|
0 |
|
|
80 |
|
|
69,340 |
|
|
|
|
|
|
|
IP Logged |
|
|
|
