Minimum value of variables 1

[dleewms]

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

 

[lbass]

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

 

[dleewms]

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

 

[lbass]

Sorry, I read the formula incorrectly.

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

-LB

 

[lbass]

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

 

[dleewms]

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.