×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Minimum value of variables

Minimum value of variables

Minimum value of variables

(OP)
I'm using Crystal Reports 2013. I've created a formula with three variables which returns absolute values. I need the formula to return the level corresponding to the smallest absolute value.

WhilePrintingRecords;

Local NumberVar tenK := Abs (ToNumber({@AcctBal}) - 10000); //Level A
Local NumberVar fiftyK := Abs (ToNumber({@AcctBal}) - 50000); //Level B
Local NumberVar OnehundredK := Abs (ToNumber({@AcctBal}) - 100000); //Level C

For example if @AcctBal = 76K then tenK = 66K, fiftyK = 26K and OnehundredK = 24K
Therefore, I would want the formula to return Level C as this is the smallest absolute value.

If there is an easier way to return needed values instead of variables, please share.

Thanks,
D. Lee

RE: Minimum value of variables

Level C will always be the minimum of the three values—-because you are always subtracting the most for that level, and the acct bal is a non-negative constant. What is the point of this? What are you trying to do?

-LB

RE: Minimum value of variables

(OP)
Thanks for your help, LB. Let me see if I can clarify a bit.

At first I thought Level C would always return the minimum value as well, but because I'm interested in the absolute value, I don't think it will be.

For example if @AcctBal = 26,000 then the absolute value of tenK = 16,000, fiftyK = 24,000 and OnehundredK = 74,000 Therefore, I'd want the formula to return Level A

Here's what I'm ultimately trying to do. In my report, I have formula fields which return the number of employees (@CtEmployee) and the account balance (@AcctBal).

Levels and portfolio size are in a separate Excel File. I need Portfolio size on my report. Each level can have multiple portfolio sizes based on the number of employees. I need to first determine the level (based on AcctBal which is CLOSEST to 10K, 50K or 100K) and once I have that, get the correct portfolio size.

Level Employee Portfolio Size
10K 10 100000
10K 25 250000
10K 50 500000
50K 10 500000
50K 25 1250000
50K 50 2500000
100K 10 1000000
100K 25 2500000
100K 50 5000000

Hopefully, this makes a little sense smile

RE: Minimum value of variables

Sorry, I read the formula incorrectly.

What is the content of your formulas {@AcctBal} and {@CtEmployees}? And of any nested formulas?

-LB

RE: Minimum value of variables

I'm assuming your formulas are summaries. Try the following to get the average balance per employee and and the corresponding levels:

numbervar balper := tonumber(sum({table.amount},{table.account}})/distinctcount({table.employee},{table.account}));//not sure what your group is--used acccount here
numbervar a := 10000;
numbervar b := 50000;
numbervar c := 100000;
numbervar y := minimum([abs(balper-a),abs(balper-b),abs(balper-c)]);
if y = abs(balper-a) then "a" else
if y = abs(balper-b) then "b" else
if y = abs(balper-c) then "c"

-LB

RE: Minimum value of variables

(OP)
Thank you very much LB.

Of course, your formula is much cleaner than mine. There were no nested formulas and the formula fields were placed in GF1.
I clearly see the minimum function is able to select the minimum from a list of values within an array [] separated by commas.

Cool! Much appreciate the help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close