Sorry this took so long. The holidays got in the way. Plus, it was a tricky operation. In the end, the only solution I could come up with was to brute force it. Well, actually, you can do it using some tricky subqueries in your SQL command. Which is probably the better method, all in all. But, I was looking for a purely Crystal solution.
The basic approach I took was to create a pair of arrays (for effectively a 2-dimensional array) and store the date and weight of each record in it. Then, run through a For loop to determine the maximum weight in each period. The formula looks like:
Global DateTimeVar Array CumDate;
Global NumberVar Array CumWgt;
Local NumberVar CurMax := 0;
Local NumberVar iLoop;
Redim Preserve CumDate[RecordNumber];
Redim Preserve CumWgt[RecordNumber];
CumDate[RecordNumber] := {Weights_.WeighDate};
CumWgt[RecordNumber] := {Weights_.Weight};
If RecordNumber > 1 Then
For iLoop := 1 to (RecordNumber - 1) Do
(If CumDate[iLoop] > DateAdd("d",-31,{Weights_.WeighDate}) And CurMax < CumWgt[iLoop] Then
CurMax := CumWgt[iLoop])
Else
For iLoop := 1 to 1 Do
(CurMax := {Weights_.Weight});
CurMax - {Weights_.Weight}
I'm hoping that most of that is fairly self-explanatory, once you step through it. I can expand on it, if you like.
There are two caveats with this method. First, arrays in Crystal are limited to 1000 members. If you have a large number of records, you may run into this limitation. The easiest solution is to add a step, in which, if UBound(CumDate) > 100 (why go all the way to 1000, when you know you don't need more than 90 days?), then run through a For..Do loop to set CumDate[iLoop] = CumDate[iLoop+1]. This effectively moves the whole array "window" through the records.
Second, you need to make sure you initialize your global variables in your report header. My solution looked like:
Global DateTimeVar Array CumDate := MakeArray(DataDate);
Global NumberVar Array CumWgt := MakeArray(0);
CumDate[1]
Conveniently enough, this also returned the date the report was run.
There are a couple other notes. First, you may notice that this formula only returns the amount of weight gained or lost. I left the process of determining the percent lost and changing it to a Yes/No marker as an exercise for the reader. Second, it is entirely possible to run this for fairly large reports with multiple patients. Simply group the records by PatientID, and re-initialize your arrays in each group header.
Hope this helps. I'd also be very curious as to whether anyone else was able to come up with a more elegant solution.
IP Logged
