Originally posted by: Thomas_321

Hello everyone,
I do have a tricky problem with an optimization problem I implemented into Cplex.

I do not have that much of experience in working with cplex, so I have reached my limits at that point and hope you can help me with this problem.

First the description of the problem:
I do have a certain number of products divided into 3 Product Categories A, B and C witch I can buy from 2 Suppliers S1 and S2 for certain prices.
I do also have a constraint that, I have to buy a certain percentage (e.g. 60%) of a certain number of product categories(e.g. 2) from supplier S1  (measured by the value of the prices).

So the first Problem is to minimize my total cost. (Decision variable x[B])
The second problem is to reach the minimum buying percentage rate for a certain number of product categories (e.g. 2). (Decision variable y[A])
(see the model below plus I attached the excel with the data)

My Problem is now the following:
y[A] should turn 1 when the buying rate of a certain product category reached the minimum percentage rate. The problem is it doesn´t.
I set the buying rate at 60% and y[A]>=3 to check the worst case szenario, for all 3 product categories y turns 1 but the actual buying rate for each category is below the 60%.

Can anybody help with this?

Thank you in advance for your time and effort!!!

 

Code:

 // Sets

{string} A = ...;     // Categories
{string} B = ...;     // Products
 
// Parameters

float c1[B] = ...;     // Product price supplier 1
float c2[B] = ...;     // Product price supplier 2
float rate = ...;     // Buying rate of supplier 1
float m = 10000;

 

// Decision variables 

dvar boolean x[B];
dvar boolean y[A];

 

// decision expression 

dexpr float test = sum (b in B)(x[b]*c1[b] +(1-x[b])*c2[b]);
 
// objective funtion  
minimize test    ;
 

// Constraints
subject to{

 

 // Constraint to assure the rate is kept

     forall (a in A)
          sum (b in B) ( x[b] * c1[b] ) >= rate * sum (b in B)(x[b]*c1[b] +(1-x[b])*c2[b])-m*(1-y[a]); 
     
     sum (a in A) y[a] >= 3;           // At least 3 product categories have to reach the minimum percentage rate
 }

 

Data:

 

SheetConnection sheet ("D:/Test/Test.xlsx");

A from SheetRead (sheet,"Tabelle1!A5:A27"); // Product Categories
B from SheetRead (sheet,"Tabelle1!B5:B27"); // Products of the respective Product Categories

c1 from SheetRead (sheet,"Tabelle1!C5:C27"); //Price 1 for the Products
c2 from SheetRead (sheet,"Tabelle1!D5:D27"); // Price 2 for the Products
rate from SheetRead (sheet,"Tabelle1!I6"); //buying Rate

x to SheetWrite (sheet,"Tabelle1!E5:E27");
y to SheetWrite (sheet,"Tabelle1!J8:J10");
test to SheetWrite (sheet, "Tabelle1!I3");

 

I hope the information is enough.

#CPLEXOptimizers
#DecisionOptimization

Since this question is related to OPL, please ask it on the OPL Forum.

#CPLEXOptimizers
#DecisionOptimization

hi,

in the results I see

 

Actual Buying Rate A	60%	1
Actual Buying Rate B	41%	1
Actual Buying Rate C	69%	1

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: Thomas_321

Hey, 

yes you are right and that´s exactly the problem.

The actual buying rate B is 41% even though it should be over 60%

Plus y[A] turned 1 even though it should because the constraints is, that y[A] should turn 1 in case of reaching the minimum buying percentage.

#CPLEXOptimizers
#DecisionOptimization

HI

 

Since all y are 1 your constraints to assure the rate is right can be simplified into

sum (b in B) ( x[b] * c1[b] ) >= rate * sum (b in B)(x[b]*c1[b] +(1-x[b])*c2[b])  ;

So I think you should check your model.

 

regards

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: Thomas_321

Hi,

thanks for your answer.

I think I didn´t manage to describe the problem I have with the second decision variable y[A] accuratly.

 

There are 3 Product categories, A,B and C and I want y[A] to assure that I reach for a defined number of Product categories the 60% minimum buying range.

 

In this particular example from above I set the constraint 

sum (a in A) y[a] >= 3;   (Therefore all product categories schould reach the minimum Buying rate of 60%!)

with the following result:

Actual Buying Rate A	60%	1
Actual Buying Rate B	41%	1
Actual Buying Rate C	69%	1

 

Which can´t be true since the buying rate of product category B is obivously below 60% therefore y[a] can´t/ shouldn´t be 1, but it is and that´s my problem.

I don´t know why it turns 1 even though the minimum buying range hasn´t been reached..

 

Anyone has an idea?

 

 

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: Thomas_321

Does anyone has an idea?

#CPLEXOptimizers
#DecisionOptimization

You need two inequalities, and you will need to accept some imprecision in one of them. To reduce typing, let B be some expression representing the actual buying rate for something, and assume a target of 0.6 (60%). As I understand it, you want y = 1 if B >= 0.6 and y = 0 if B < 0.6. The strict inequality will present a problem.

Start with B >= 0.6 - M*(1-y), which is what you already have. Add the inequality B <= 0.6 + K*y, where K is another sufficient large constant (not necessarily equal to M). Now y = 1 => B >= 0.6 by the first inequality (the second being vacuous), while y = 0 => B <= 0.6 by the second inequality (the first being vacuous).

The one small (?) drawback is that if B is exactly 0.6 (to within rounding tolerance), y can be either 0 or 1. An alternative is to change 0.6 to 0.6 - epsilon in the second constraint, for some small (but not too small) positive epsilon. With that change, B = 0.6 unambiguously results in y = 1, but there is no free lunch to be had: you have now made any value of B between 0.6 - epsilon and 0.6 infeasible.

 

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: Thomas_321

Hey Paul,

thank you for your answer!

I tried your solution with the second constraint but it still doesn´t work. I get the following results.

Saying that for product category A, B and C I reached the minimum Buying rate of 65% but this is only correct for product category C.

 

I´m starting to wonder if I made a mistake in the implementation of the excel files.

The way I´m implementing them now is, I assume OPL is able to "understand" which product belongs to which product category.  Is that possible or do I have to assign every product the the respective product category somehow? 

 

#CPLEXOptimizers
#DecisionOptimization

This is drifting out of the realm of IP and into the realm of OPL. You might have a better shot at an answer on the OPL forum.

#CPLEXOptimizers
#DecisionOptimization

Originally posted by: Thomas_321

I´ll try it there. 

Thank you!

#CPLEXOptimizers
#DecisionOptimization