I am working in Crystal Reports 11 and would like to know how to do a geomean calculation. I have my data grouped by Date and I am setting up a manual crosstab so out of all the data I have one set of data that I need to do the Geomean calculation on. My result is a text so I use the following calculation to get a number:
if Left ({RESULT.RESULT},1) = "<" then
val ({RESULT.MDL})
else if Left ({RESULT.RESULT},1) = ">" then
val (Mid ({RESULT.RESULT},2))
else
val ({RESULT.RESULT})
but because I want to do the calculation only on one set of data I redid my number formula to:
if {RESULT.ANALYTE} = "Escherichia coli" then (val ({RESULT.RESULT}) / 1000000)
else
val ({RESULT.RESULT})
I followed a thread in this forum with the following information:
So Formulas should read:
//{@initialize} to be placed in the report header........I placed this in the header
whileprintingrecords;
global numbervar x := 1; //Product variable
global numbervar c:=0; //counter variable
//{@accum} to be placed in the detail section:...........I placed this in the detail section
whileprintingrecords;
global numbervar x;
global numbervar c;
If {table.qty}> 0 then (
x:= x * {table.qty} /30;
c:= c + 1
//Result formula in the group or report footer:........I placed this in the Group Footer
whileprintingrecords;global numbervar x;
global numbervar c;if c=0 then 0 else 30 * (x ^ (1/c))
I tried this but did not get the result I would have gotten through Excel. On some days I will have two or three results so I need to find out the Geomean for that one day but this could happen a few times each month.
If it would help I could send the few lines I have but any assistance would be appreciated as I have never posted to a forum before I'm not sure if you will have too much information or not enough. Thanks a lot.
if Left ({RESULT.RESULT},1) = "<" then
val ({RESULT.MDL})
else if Left ({RESULT.RESULT},1) = ">" then
val (Mid ({RESULT.RESULT},2))
else
val ({RESULT.RESULT})
but because I want to do the calculation only on one set of data I redid my number formula to:
if {RESULT.ANALYTE} = "Escherichia coli" then (val ({RESULT.RESULT}) / 1000000)
else
val ({RESULT.RESULT})
I followed a thread in this forum with the following information:
So Formulas should read:
//{@initialize} to be placed in the report header........I placed this in the header
whileprintingrecords;
global numbervar x := 1; //Product variable
global numbervar c:=0; //counter variable
//{@accum} to be placed in the detail section:...........I placed this in the detail section
whileprintingrecords;
global numbervar x;
global numbervar c;
If {table.qty}> 0 then (
x:= x * {table.qty} /30;
c:= c + 1
//Result formula in the group or report footer:........I placed this in the Group Footer
whileprintingrecords;global numbervar x;
global numbervar c;if c=0 then 0 else 30 * (x ^ (1/c))
I tried this but did not get the result I would have gotten through Excel. On some days I will have two or three results so I need to find out the Geomean for that one day but this could happen a few times each month.
If it would help I could send the few lines I have but any assistance would be appreciated as I have never posted to a forum before I'm not sure if you will have too much information or not enough. Thanks a lot.
