Solved

Need help with a crystal formula

Posted on 2013-12-26
17
461 Views
Last Modified: 2013-12-26
I have this formula that only works with the first line. When adding the second line I get an error

This works with no errors
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} 

Open in new window


When adding the second line I get (see image below):
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE02}

Open in new window


a boolean is required
This is the final formula that I need

if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE02} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE03} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE04} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE05} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE06} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE07} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE08} and
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09}

Open in new window

0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 5
  • +1
17 Comments
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740293
Change all your 'and' s to else.
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE01} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE02} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE08} else
if {SALES_ORDER_DETAIL.BVORDQTY} <= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09}
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740345
Personally, when I have a finite list of possible combinations, I like to use a SWITCH function, which is the functional equivalent of an IF-THEN-ELSE that has no default "ELSE" value at the end.  Also, if I'm going to have one value repeated constantly, I like to replace it with a variable to make things easier to read.  Here's an example:

LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};

SWITCH
(
QTY <= {PRICING.BVBREAKQTY01},{PRICING.BVBREAKPRICE01},
QTY <= {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE02},
QTY <= {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE03},
QTY <= {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE04},
QTY <= {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE05},
QTY <= {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE06},
QTY <= {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE07},
QTY <= {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE08},
QTY <= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
)

Open in new window

0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39740365
Ok changing to else works but my formula does not work.

@ rhinok, I like your idea better but I have a hard time figuring out how to use the <= and >=

Let's assume my QTY = 25. In this case my cost price should be $250

Here is my table for the BVBREAKQTY (Quantity) and BVBREAKPRICE (Cost price)

qty price
How would I phrase my formula?
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 100

Expert Comment

by:mlmcc
ID: 39740389
Try

LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};

SWITCH
(
QTY <= {PRICING.BVBREAKQTY01},{PRICING.BVBREAKPRICE01},
QTY > {PRICING.BVBREAKQTY01} AND QTY <= {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE02},
QTY > {PRICING.BVBREAKQTY02} AND QTY <= {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE03},
QTY > {PRICING.BVBREAKQTY03} AND QTY <= {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE04},
QTY > {PRICING.BVBREAKQTY04} AND QTY <= {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE05},
QTY > {PRICING.BVBREAKQTY05} AND QTY <= {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE06},
QTY > {PRICING.BVBREAKQTY06} AND QTY <= {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE07},
QTY > {PRICING.BVBREAKQTY07} AND QTY <= {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE08},
QTY > {PRICING.BVBREAKQTY08} AND QTY <= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
) 

Open in new window


mlmcc
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39740457
It is very close to working

When QTY = 42 I get 180 but it should be 190
When I have QTY >= 51 then I get 0.00 but it should be 170
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740467
Based on your example, try this:

LOCAL NUMBERVAR QTY := {SALES_ORDER_DETAIL.BVORDQTY};

SWITCH
(
QTY < {PRICING.BVBREAKQTY02},{PRICING.BVBREAKPRICE01},
QTY >= {PRICING.BVBREAKQTY02} AND QTY < {PRICING.BVBREAKQTY03},{PRICING.BVBREAKPRICE02},
QTY >= {PRICING.BVBREAKQTY03} AND QTY < {PRICING.BVBREAKQTY04},{PRICING.BVBREAKPRICE03},
QTY >= {PRICING.BVBREAKQTY04} AND QTY < {PRICING.BVBREAKQTY05},{PRICING.BVBREAKPRICE04},
QTY >= {PRICING.BVBREAKQTY05} AND QTY < {PRICING.BVBREAKQTY06},{PRICING.BVBREAKPRICE05},
QTY >= {PRICING.BVBREAKQTY06} AND QTY < {PRICING.BVBREAKQTY07},{PRICING.BVBREAKPRICE06},
QTY >= {PRICING.BVBREAKQTY07} AND QTY < {PRICING.BVBREAKQTY08},{PRICING.BVBREAKPRICE07},
QTY >= {PRICING.BVBREAKQTY08} AND QTY < {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE08},
QTY >= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
) 

Open in new window

0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740481
Just try this:

if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE09} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE08} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY01} then {PRICING.BVBREAKPRICE02} else
{PRICING.BVBREAKPRICE01}
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740498
From the OP:
When QTY = 42 I get 180 but it should be 190
if {SALES_ORDER_DETAIL.BVORDQTY} > {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE08} else

Open in new window

Using the formula above, an Order Qty of 42 would still result in 180 when it should be 190.  

In order to return Break Price 7, based on the scenario provided by the OP, the Order Qty needs to be >= Break Qty 7 and < Break Qty 8.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39740515
@13Shadow
I get the same results with your formula except for >50 does works.

I'm confused...I know enough about writing formulas to get myself in trouble, which is the case here.

@ rhinok I'll appreciate if you could help with the finish formula.
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740533
Oh yeah I didn't see that comment.
The original post was asking for a solution to the formula failing with errors.
My solution was a valid one correcting the syntax error due to the invalid use of and.
0
 
LVL 3

Expert Comment

by:13Shadow
ID: 39740583
Try this:
if {SALES_ORDER_DETAIL.BVORDQTY} >= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY02} then {PRICING.BVBREAKPRICE01} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY03} then {PRICING.BVBREAKPRICE02} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY04} then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY05} then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY06} then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY07} then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY08} then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE08}
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740621
My last code sample accounts for your scenario.  13Shadow's last formula should work, too.  It basically does the same thing as mine, except it relies on implicit predicate order instead of explicitly declaring ranges.

FYI - it doesn't really matter if you use the IF-THEN-ELSE or the SWITCH (or even a SELECT) in this case.  They'll all accomplish the same thing.  I personally just prefer the "cleanliness" of one vs. the other.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39740734
@13Shadow
Your formula works now but one more thing that I did not see coming

In most cased only the first 3 lines have qty break pricing. Now my formula for QTY 25 looks at BVBREAKQTY09 and print 0.00 because BVBREAKQTY09 = 0 for qty

Here is what it looks like in the live data
qty break
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39740788
So, in most cases, there are only three breaks?  Is the maximum number of breaks always going to be nine or can it be even more? In the example above, you would want to show $256 as the price then the ordered QTY was >= 25, right?  In your example above, you explicitly list "0" as values for unpopulated breaks.  Will that always be the case or will those fields be NULL?


Assuming there is a maximum of nine possible breaks AND "unused" breaks are populated with zeroes, try the following:

//The following variable is to evaluate and assign the order quantity
LOCAL NUMBERVAR OQTY := {SALES_ORDER_DETAIL.BVORDQTY};

//The following variables are to evaluate and assign the various possible break quantities
LOCAL NUMBERVAR BQTY2:= {PRICING.BVBREAKQTY02};
LOCAL NUMBERVAR BQTY3:= {PRICING.BVBREAKQTY03};
LOCAL NUMBERVAR BQTY4:= {PRICING.BVBREAKQTY04};
LOCAL NUMBERVAR BQTY5:= {PRICING.BVBREAKQTY05};
LOCAL NUMBERVAR BQTY6:= {PRICING.BVBREAKQTY06};
LOCAL NUMBERVAR BQTY7:= {PRICING.BVBREAKQTY07};
LOCAL NUMBERVAR BQTY8:= {PRICING.BVBREAKQTY08};
LOCAL NUMBERVAR BQTY9:= {PRICING.BVBREAKQTY09};

SWITCH
(
OQTY < BQTY2 OR BQTY2 = 0,{PRICING.BVBREAKPRICE01},
OQTY >= BQTY2 AND (OQTY < BQTY3 OR BQTY3 = 0),{PRICING.BVBREAKPRICE02},
OQTY >= BQTY3 AND (OQTY < BQTY4 OR BQTY4 = 0),{PRICING.BVBREAKPRICE03},
OQTY >= BQTY4 AND (OQTY < BQTY5 OR BQTY5 = 0),{PRICING.BVBREAKPRICE04},
OQTY >= BQTY5 AND (OQTY < BQTY6 OR BQTY6 = 0),{PRICING.BVBREAKPRICE05},
OQTY >= BQTY6 AND (OQTY < BQTY7 OR BQTY7 = 0),{PRICING.BVBREAKPRICE06},
OQTY >= BQTY7 AND (OQTY < BQTY8 OR BQTY8 = 0),{PRICING.BVBREAKPRICE07},
OQTY >= BQTY8 AND (OQTY < BQTY9 OR BQTY9 = 0),{PRICING.BVBREAKPRICE08},
OQTY >= BQTY9,{PRICING.BVBREAKPRICE09}
)

Open in new window

You don't necessarily need all the variables, but it makes the code a littler easier to read, since you won't have the field names replicated so many times.
0
 
LVL 3

Assisted Solution

by:13Shadow
13Shadow earned 250 total points
ID: 39740837
Try this:
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY02} or {PRICING.BVBREAKQTY02} = 0 then {PRICING.BVBREAKPRICE01} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY03} or {PRICING.BVBREAKQTY03} = 0 then {PRICING.BVBREAKPRICE02} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY04} or {PRICING.BVBREAKQTY04} = 0 then {PRICING.BVBREAKPRICE03} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY05} or {PRICING.BVBREAKQTY05} = 0 then {PRICING.BVBREAKPRICE04} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY06} or {PRICING.BVBREAKQTY06} = 0 then {PRICING.BVBREAKPRICE05} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY07} or {PRICING.BVBREAKQTY07} = 0 then {PRICING.BVBREAKPRICE06} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY08} or {PRICING.BVBREAKQTY08} = 0 then {PRICING.BVBREAKPRICE07} else
if {SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY09} or {PRICING.BVBREAKQTY09} = 0 then {PRICING.BVBREAKPRICE08} else
if {SALES_ORDER_DETAIL.BVORDQTY} >= {PRICING.BVBREAKQTY09} then {PRICING.BVBREAKPRICE09}
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 250 total points
ID: 39740884
Here's the SWITCH equivalent of the above statement (including variables).

//The following variable is to evaluate and assign the order quantity
LOCAL NUMBERVAR OQTY := {SALES_ORDER_DETAIL.BVORDQTY};

//The following variables are to evaluate and assign the various possible break quantities
LOCAL NUMBERVAR BQTY2:= {PRICING.BVBREAKQTY02};
LOCAL NUMBERVAR BQTY3:= {PRICING.BVBREAKQTY03};
LOCAL NUMBERVAR BQTY4:= {PRICING.BVBREAKQTY04};
LOCAL NUMBERVAR BQTY5:= {PRICING.BVBREAKQTY05};
LOCAL NUMBERVAR BQTY6:= {PRICING.BVBREAKQTY06};
LOCAL NUMBERVAR BQTY7:= {PRICING.BVBREAKQTY07};
LOCAL NUMBERVAR BQTY8:= {PRICING.BVBREAKQTY08};
LOCAL NUMBERVAR BQTY9:= {PRICING.BVBREAKQTY09};

SWITCH
(
OQTY < BQTY2 OR BQTY2 = 0,{PRICING.BVBREAKPRICE01},
OQTY < BQTY3 OR BQTY3 = 0,{PRICING.BVBREAKPRICE02},
OQTY < BQTY4 OR BQTY4 = 0,{PRICING.BVBREAKPRICE03},
OQTY < BQTY5 OR BQTY5 = 0,{PRICING.BVBREAKPRICE04},
OQTY < BQTY6 OR BQTY6 = 0,{PRICING.BVBREAKPRICE05},
OQTY < BQTY7 OR BQTY7 = 0,{PRICING.BVBREAKPRICE06},
OQTY < BQTY8 OR BQTY8 = 0,{PRICING.BVBREAKPRICE07},
OQTY < BQTY9 OR BQTY9 = 0,{PRICING.BVBREAKPRICE08},
OQTY >= BQTY9,{PRICING.BVBREAKPRICE09}
)

Open in new window

Here's the same thing, minus all variables:
SWITCH
(
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY02} OR {PRICING.BVBREAKQTY02} = 0,{PRICING.BVBREAKPRICE01},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY03} OR {PRICING.BVBREAKQTY03} = 0,{PRICING.BVBREAKPRICE02},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY04} OR {PRICING.BVBREAKQTY04} = 0,{PRICING.BVBREAKPRICE03},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY05} OR {PRICING.BVBREAKQTY05} = 0,{PRICING.BVBREAKPRICE04},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY06} OR {PRICING.BVBREAKQTY06} = 0,{PRICING.BVBREAKPRICE05},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY07} OR {PRICING.BVBREAKQTY07} = 0,{PRICING.BVBREAKPRICE06},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY08} OR {PRICING.BVBREAKQTY08} = 0,{PRICING.BVBREAKPRICE07},
{SALES_ORDER_DETAIL.BVORDQTY} < {PRICING.BVBREAKQTY09} OR {PRICING.BVBREAKQTY09} = 0,{PRICING.BVBREAKPRICE08},
{SALES_ORDER_DETAIL.BVORDQTY} >= {PRICING.BVBREAKQTY09},{PRICING.BVBREAKPRICE09}
)

Open in new window

Either of these could be swapped for 13Shadow's latest code, but doing so would be purely a matter of preference.  All three statements do the exact same thing. I'm simply just demonstrating differences in syntax at this point.   If any of these statements give you the results you want, great!
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 39740894
@rhinok
You are right both yours and @13Shadow are working.

Thank you to both of you  for your help.

To do these formulas is way beyond me...

Best for the new year!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question