Help please in working with arrays and groups

[ComputerNurse]

CR v8.5 SQL DB

I am writing a report to calculate weight variances for 30 60 and 180 days. The formula is 100- (current weight / previous weight).

I thought I should put the weights and the dates in arrays and make comparisions. I ended up putting the weights in an array that resets the counter at each group change. I am not sure how to write the formula to evaluate the arrays only with in the group in the group.

Numbervar array wtarray; 
Numbervar counter; 
counter = 0;

If onfirstrecord 
   then counter:= 1

  Else If not onfirstrecord and{Masterfile.ResidentNumber} = previous({ResidentMasterfile.ResidentNumber})
    then counter := counter + 1

     Else if not onfirstrecord and Masterfile.ResidentNumber} <> previous({Masterfile.ResidentNumber})
      then counter := 1;

  
 If counter <=1000
   then (redim preserve wtarray[counter];
     wtarray[counter]:={MR Tracking D.CurrentWeight});

The detail looks like this


Name Date Weight View contents (Test only)
-------- ------ ----- -------------
Jones 1/1/2004 99 Added to wtarray[1]
2/1/2004 97 Added to wtarray[2]
3/1/2004 98 Added to wtarray[3]


Smith 1/1/2004 150 Added to wtarray[1]
2/1/2004 155 Added to wtarray[2]


**I need to be able to do is take the amount in for wtarray[3] jones 98lbs and divide it by 97 wtarray[2].
Also the dates must be 30 days or else no calculation is done.

 

[bdreed35]

I am not sure that you need that arrays for this.

Try creating this formula and putting it int he patient name group footer:

If count({table.weight},{table.group field}) >= 2 and 
   DateDiff("d",previous({table.date}),{table.date}) = 30 then
    {table.weight} / previous({table.weight});

Replace {table.weight} with the field in the weight column.
Replace {table.group field} with whatever field you are using for the patient group.
Replace {table.date} with the field in the date column.

~Brian

 

[ComputerNurse]

Thanks Brian that worked wonderfully. On to my next task,I must also peform similar calcuations finding a date with a weight that is 60/90/180 days prior to the current weight. I tried accessing these values by.

Datevar array datearray;
Numbervar datecounter;
Numbervar array wtarray; 
Numbervar counter; 


If datediff ("d", datearray[maximum(datecounter)],datearray[maximum(datecounter)-1]) = 60 then
previous({MR.CurrentWeight})/({MR  D.CurrentWeight})

Am I on the right track? I am recieving an error that the summary total field cannot be created...