Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Return column value adjacent to max value Post Reply Post New Topic
Author Message
feelgoodlost
Newbie
Newbie


Joined: 09 Aug 2011
Online Status: Offline
Posts: 5
Quote feelgoodlost Replybullet Topic: Return column value adjacent to max value
     Posted: 09 Aug 2011 at 6:22am
I have a report, grouped by an account number with a time column and value column.  At the end of each account group, I want to display the absolute maximum value and the time it occured at.  Something like this:
 
Account: XYZ123
 
TIME        VALUE
12:15      100.32
14:00      -123.15
18:30      115.64
 
ABS Max: 123.15  Time: 14:00
 
I can easily return the absolute maximum using a formula field, but I'm not sure how to return the time when the maximum occurred in the adjacent column.  Could someone help me out with the formula to do this?
 
this is what I'm doing at the moment for the ABS max formula field:
 
numbervar min = 0;
numbervar max = 0;
min := ABS(Minimum({REPORTS_PKG_TEST.VALUE}, {REPORTS_PKG_TEST.ACCOUNT}));
max := ABS(Maximum({REPORTS_PKG_TEST.VALUE}, {REPORTS_PKG_TEST.ACCOUNT}));
IF min > max THEN
    min
ELSE
    max;
 
I'm new to this form and very new to Crystal Reports.  I'm hoping someone could help me out - I assume this would be easy for someone more experienced, but who knows.  Thanks!
 
EDIT: forgot to mention, I'm using Crystal Reports XI


Edited by feelgoodlost - 09 Aug 2011 at 6:31am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 10 Aug 2011 at 2:30am
why not a formula like:
datetimevar aTime;
numbervar aNum;
local numbervar temp := ABS({table.field});
 
if temp > aNum then(
 aTime := {table.timefield};
 aNum := temp;
)
""//hides the formula
 
in the group footer, 2 formulas:
numbervar aNum;
aNum
 
&
 
datetimevar aTime;
aTime
 
 
in the group header, a reset:
datetimevar aTime := "1/1/1900";
numbervar aNum = 0;
""//again hides formula
 
 
HTH
 
 
 
IP IP Logged
feelgoodlost
Newbie
Newbie


Joined: 09 Aug 2011
Online Status: Offline
Posts: 5
Quote feelgoodlost Replybullet Posted: 10 Aug 2011 at 11:57am

Thanks a lot for the reply, I don't completely understand though.

Where would I put the first chunk of code to keep track of the max aNum and aTime? Would this be in another formula field that I would put in the details section? At the moment, I just have the one formula field in the group footer with the code I posted. So I created a new formula field for the header and details and put the code in as you indicated but I am getting nothing returned for my ABS MAX (@VALUE PEAK) in the group total.

I think I see what you're saying: for every detail row, check if the absolute value is higher and if so save it in and the time in global variables to display in the group footer.  It's just a matter of where crystal wants me to put this code.

Here's what I did:

in a new header formula field(@VALUE_HEAD):
datetimevar aTime := "1/1/1900";
numbervar aNum := 0;
""//again hides formula


in a new detail formula field (@VALUE):
datetimevar aTime;
numbervar aNum;
local numbervar temp := ABS({REPORTS_PKG_TEST.VALUE});
 
if temp > aNum then(
 aTime := {REPORTS_PKG_TEST.TIME};
 aNum := temp;
);
""//hides the formula


in the group footer formula field (@VALUE_PEAK):
numbervar aNum;
aNum
 
&
 
datetimevar aTime;
aTime;


sorry, quite new to this.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 11 Aug 2011 at 3:12am
that is the gist of it.  header - reset, detail - increment, footer - display.
 
you can only display 1 value per formula, but that is minor.
 
to debug, remove "" at the end of the incrementing formula, this will allow you to see 'something'.  you might want to change it something like this for debugging...
datetimevar aTime;
numbervar aNum;
local numbervar temp := ABS({REPORTS_PKG_TEST.VALUE});

if temp > aNum then(
aTime := {REPORTS_PKG_TEST.TIME};
aNum := temp;
);
aNum
 
 
CR will display whatever the last item is, which is why "" hides the output of the formula.
 
HTH
IP IP Logged
feelgoodlost
Newbie
Newbie


Joined: 09 Aug 2011
Online Status: Offline
Posts: 5
Quote feelgoodlost Replybullet Posted: 11 Aug 2011 at 5:16am
I got it to work by scoping the variables as shared and using 2 group total formulas instead of 1 for diplaying both values, like you say.
 
shared numbervar aNum;
shared datetimevar aTime;
 
thanks again
IP IP Logged
feelgoodlost
Newbie
Newbie


Joined: 09 Aug 2011
Online Status: Offline
Posts: 5
Quote feelgoodlost Replybullet Posted: 11 Aug 2011 at 6:33am
well, I thought it was working.  I only looked at the first group totals.  The value is not getting reset.  I debugged by allowing the value to show, and I see that the value is 0 as expected for the first group header.  Then I see the value get set as a new max is found.  Looks good.  But when I get to the next group header, even though I'm resetting the value to 0, it still shows the value as it was last set.  So even though I am explicitly saying
shared numbervar aNum;
aNum = 0;
aNum;
this displays a number other than 0 :-(
 
IP IP Logged
feelgoodlost
Newbie
Newbie


Joined: 09 Aug 2011
Online Status: Offline
Posts: 5
Quote feelgoodlost Replybullet Posted: 11 Aug 2011 at 8:48am
aaand it's because I had aNum = 0 instead of aNUm := 0 .... Pinch
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.