Crystal Reports 13 Standard Deviation with formula fields

Question

I've been working on a report made by someone else which uses the StDev function. I thought it would be simple enough, but the data can contain multiple values for each record, so there are if statements used to determine which value to take from each record. I've exported a table to Excel which contains one value per record (the one that should be used in the StDev), and then calculated the SD there to provide a check.

The report and Excel are giving me very different values :(

So, I'm going back to the report and using some additional formula fields to calculate the SD longhand to act as a kind of deciding vote (fingers crossed it doesn't produce a third set of values...).

I've worked out the syntax errors, but am still getting a run-time error - "Division by zero" which then highlights the section of code indicated below...

My formula fields are:

{@StDevArrayPopulate} - in the details section

NumberVar Array varStDevArray;
NumberVar varStDevArrayCount;

varStDevArray [varStDevArrayCount] := {ValueToSummarise};

varStDevArrayCount := varStDevArrayCount + 1;

{@StDevArrayCalculate} - in the group footer

NumberVar Array varStDevArray;
NumberVar varCounter :=1;
NumberVar varMean := 0;
NumberVar varStDev := 0;
NumberVar varStDevArrayCount;

// START OF MEAN

// Sum of all of the values in the array

for varCounter:= 1 to varStDevArrayCount step 1 do
(
    varMean := varMean + varStDevArray [varStDevArrayCount];
);

// Divide by the total number of values in the array

varMean := varMean / varStDevArrayCount;  // !! This is the line that highlights after the error message !!

// END OF MEAN

// START OF STANDARD DEVIATION

// Subtract the mean from each value in the array and square the result

for varCounter := 1 to varStDevArrayCount step 1 do
(
    varStDevArray[varStDevArrayCount] := (varStDevArray [varStDevArrayCount] - varMean) * (varStDevArray [varStDevArrayCount] - varMean);
);

// Sum of all of the values in the array

for varCounter:= 1 to varStDevArrayCount step 1 do
(
    varStDev := varStDev + varStDevArray [varStDevArrayCount];
);

// Divide by the total number of values in the array

varStDev := varStDev / varStDevArrayCount;

// Square root of mean of differences

varStDev := Sqr(varStDev)

// END OF STANDARD DEVIATION

{@StDevArrayCalculate} - in the group footer

NumberVar varMean;
NumberVar varStDev;

"The mean is " & varMean & ", and the standard deviation is " & varStDev & "."

I've tried using a Running Total field but that was giving errors as it need to be count the first record before the Populate formula field ran. I've also tried adding a fourth field to the header, which initialises the varStDevArrayCount as 1.

Does anyone have any suggestions?

score 0 · Answer 1 · answered Jul 01 '16 at 09:16

Looks like there are no detail records hence you are getting zero in array... I don't think initializing with 1 will make any difference instead make a small change like below and check.

if varStDevArrayCount=0
then
varMean :=varMean 
else
varMean := varMean / varStDevArrayCount;

I would advice you to just check records in detail and then debug

Crystal Reports 13 Standard Deviation with formula fields

1 Answers1