Print Page | Close Window

Cannot sum calculated and subsequent fiels

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12616
Printed Date: 05 May 2024 at 8:08am


Topic: Cannot sum calculated and subsequent fiels
Posted By: zander
Subject: Cannot sum calculated and subsequent fiels
Date Posted: 15 Mar 2011 at 4:54am
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
 


Replies:
Posted By: lockwelle
Date Posted: 16 Mar 2011 at 3:42am
you can't use previous and a formula.  in addition you can't use a aggregate function like sum, avg, stddev on a computed field (it doesn't know the value at the right time)
 
if you just need the previous value, you can create a variable to hold the @vol_mid, but that doesn't get you around the std dev issue.  Unfortunately, the only solution to that is implement in formulas what the computational steps are for standard deviation, which is something like finding out the average distance from the average that the values are...you should be able to easily find the formula on a statistics site.
 
HTH

Posted By: zander
Date Posted: 16 Mar 2011 at 4:53am
Thank you for the reply, I hoped there was an easier way though. Could you please help me with getting the previous value using the variable method?

Posted By: lockwelle
Date Posted: 16 Mar 2011 at 7:10am
shared numbervar lastVal;
shared numbervar thisVar;
 
if NOT ISNULL(thisVar) then
  lastVal := thisVar;
 
//do logic to get this thisVar
 
I would probably do this where the vol_mid is defined, then you can reference the lastVal variable in the other formulas.  just need to add:
shared numbervar lastVal;
 
to the formulas
 
HTH

Posted By: zander
Date Posted: 16 Mar 2011 at 11:21pm
I dont seem to get it working
for @vol_mid i used
whileprintingrecords;
shared numbervar thisvar;
thisvar:=(({volrates.vol_bid}+{volrates.vol_offer})/2)

for @previousrecord I tried
whileprintingrecords;
shared numbervar lastVal;
shared numbervar thisVar;
if NOT ISNULL(thisVar) then
  lastVal := thisVar;
the error is @field is required here (referrign to 'thisVar')

I tried the following but then @previous = @vol_mid
whileprintingrecords;
shared numbervar lastVal;
shared numbervar thisVar;
 
if recordnumber = 1 then lastval:=0 else
    lastval:=thisvar

I think I need to code the following logic but I am not finding a solution
if recordnumber = 1 then thisvar=@volmid and lastval=0
else lastval = thisvar (value of the first record) , thisvar should tehn change to the value of the current@volmid and the next lastval = thisvar.
 every way I code it thisvar assumes the @volmid of the current record and not of the previous record.

what am I missing?






Posted By: lockwelle
Date Posted: 17 Mar 2011 at 3:16am
well, now that you mention it, (the record number =1 jogged my thinking)you could try something like:
 
whileprintingrecords;
shared numbervar lastVal;
shared numbervar thisVar;
 
if onFirstRecord then
  lastval:=0
else
  lastval:=thisvar;
 
thisvar = mailto:%7b@mid_vol - {@mid_vol };
 
does this work any better?

Posted By: zander
Date Posted: 17 Mar 2011 at 3:37am
No, it returns FALSE. The problem I found with having thisvar anywhere else but where it is linked to record 1, is that it takes the value of the current record's @vol_mid, somehow you need to specify that on hte first record thisvar must be @vol_mid but lastval = 0
on the following record thisvar starts off as the @vol_mid of record 1 and then lastval = thisvar but agter writing this value thisvar now need to change  to @vol_mid of the current record.


Print Page | Close Window