Cannot sum calculated and subsequent fiels

[JohnnyBlack]

In crystal reports 11, I need to calculate the change between records and use the result elsewhere, but the field does not list the summary function in the shortcut menu and if I create a formula I get the "this field cannot be summarised" error"

My dataset is a list of rates at various dates.
Fields include:
@vol_mid = ({volrates.vol_bid}+{volrates.vol_offer})/2
@Previous record = Previous({@vol_mid}) (this could be the source of the error since I cannot summarise this field).
@log_dailychange = log({@vol_mid}/{@Previous record})
@sd_change = stddev(@log_dailychange) ....error = "this field cannot be summarised"

Q1: how can I use summary functions on @previous record
Q2:how can I calculate the stddev for the daily change

I read on other threads that the whileprintrecords plus a variable is used to sum the variable but I have no idea how this would work for a stddev.

Is there an easier/any way to do this. I am not a programmer by nature so I have limited knowledge in areas where you do not see the functions in the formula editor.

Thank you for your assistance

 

[lbass]

No, you will have to use variables, so the next step is for you to supply the formula for a standard deviation, as I for one, don't know it.

-LB

 

[JohnnyBlack]

Thank you for the reply. I assume if I use varaibles to get the prev val I will at least be able to calc the avg since this is the basis of the std dev formula.
I pretty much have the steps to calulate the std dev manually but have no idea how to use the variables to get the previous value. If you could assist in this I'm sure I'll get the rest figured out.

 

[lbass]

You don't have to use the variable method to capture the previous record--you can use your existing formula for that. But what you can do is summarize your formula with a variable.

For example, to add the formula, you would use:

whileprintingrecords;
numbervar x := x + {@log_dailychange};

You would place this in the section where {@log_dailychange} correctly displays, e.g., the detail section, in order to accumulate the value. If you are summarizing at a group level, you would place a reset formula in the group header:

whileprintingrecords;
numbervar x;
if not inrepeatedgroupheader then
x := 0;

For the result, you would place the following formula in the group footer:

//{@display}:
whileprintingrecords;
numbervar x;

You could work with the display formula by adding calculations within the formula, e.g.:

whileprintingrecords;
numbervar x;
x*{table.amt}

-LB

 

[JohnnyBlack]

I got the running total
@dailychnage_rt=
whileprintingrecords;
numbervar x := x + {@dailychange};
and the display total
@dailychnage_sum =
//{@display}:
whileprintingrecords;
numbervar x;

From here I added an average
@dailychange_avg=
whileprintingrecords;
numbervar y:={@dailychange_sum} /{@cnt_records}
which gives the correct total in the GF
and a display avg
@gt_avg =
//{@display}:
whileprintingrecords;
numbervar y;

in my next step I need to subtract the avg (group total or the final y) from @dailychange (of each record)
@dailychange_avg_vs_dc =
whileprintingrecords;
numbervar y;
(y-{@dailychange})^2
this however gives me the an incorect total since it seem to use a kind of running total for the avg and not the value of Y (@gt_avg)
how can I get it to subtract each record from a constant avg?

 

[lbass]

Don't use the formulas, use the variables directly. In fact, I wouldn't nest formulas (other than orginal one):

//@dailychange_avg
whileprintingrecords;
numbervar x;
numbervar z;
numbervar y:= x/z;

...where numbervar z := z + 1; was added to your daily change rt formula.

I don't know the content of your dailychange formula or your count formula. If you are cteating these results at the group footer level, you need a reset formula that resets all variables in the group header as I earlier indicated.

whileprintingrecords;
numbervar x;
numbervar y;
numbervar z;
if not inrepeatedgroupheader then (
x := 0;
y := 0;
z := 0
);

-LB

 

[JohnnyBlack]

The problem is in the following step, the dailychange is working fine.
Since I cannot calculate the avg using the AVERAGE function I add all the detail records of @dailychange and then calculate the an average using count which the number of records (detail level, see columns below). So in the next step (see @dailychange_avg_vs_dc below), for each record on detail level I need to calc the difference between the average (the group footer total or average of the group; 20 in the example below) and the @dailychange value for each record (10;20;30 below). The calculations (using whileprinting and variables) seem to be done per record (on detail level)it uses the avg at that record and not the avg for the group(20). below is what I am after

@daily change(column) @dailychange_avg_vs_dc
10 20-10=10
20 20-20=0
30 30-20=10
20=Avg(60/3) Sum=10
(20=y in the previous post, I used y in an efford to refer to the total avg but this value is not constant(20) at each record)

@dailychange=difference between current record and previous record(using previous(rate) as the formula.
@count is the number of records in the group (using the count formula).

 

[lbass]

Okay, yes--that was why I was wondering what the calculation entailed. Since the average isn't calculated yet, the only way you could do this is by saving the report as a subreport, linking it appropriately, placing it in the group header (assuming this calculation is at some group level), and then setting up the average as a shared variable that you can then access in the main report at the detail level.

-LB