Vaibhav - 1 year ago 185

R Question

We have the below code for solving an optimization problem where we want to maximize sales by applying constraint on profit and no. of items.

We want to apply this profit threshold as a percentage of Revenue generated by 200 items only.

We have done it by applying a formula on profit using changing variable in Excel Solver using GRGE non-linear algorithm. We want a similar alternative for R.

Is there any way to assign changing variable in R?

**Dataset**

`item sales profit`

A 1200 120

B 5600 45

C 450 00

D 990 -90

E 1000 80

F 560 120

G 500 23

H 2000 350

`library(lpSolveAPI)`

dataset<-read.csv("Dataset.csv",header=T,na.strings='NA',stringsAsFactors =F)

dataset$keep_flag <-1

**all the func in LPsolve API**

ls("package:lpSolveAPI")

summary(dataset)

**Passing the parameters**

ncol <- nrow(dataset)

**you have eight rows that can be picked or dropped from the solution set**

lp_rowpicker <- make.lp(ncol=ncol)

set.type(lp_rowpicker, columns=1:ncol, type = c("binary"))

**checking the model**

lp_rowpicker

**setting objective**

obj_vals <- dataset$Revenue_1hr.Projected

#obj_vals<- dataset[, 2]

obj_vals

set.objfn(lp_rowpicker, obj_vals)

lp.control(lp_rowpicker,sense='max')

**Adding contraints**

Profit constraint

xt<- (dataset$Profit_1hr.Projected)

add.constraint(lp_rowpicker, xt, ">=", 100)

xt

#No.of items to be kept

xt<- (dataset$keep_flag)

add.constraint(lp_rowpicker, xt, "=", 4)

xt

#model check

lp_rowpicker

#solving equation

solve(lp_rowpicker)

#Maximised revenue

get.objective(lp_rowpicker)

#The one with binary as 1 is our item

dataset$keep_flag<- get.variables(lp_rowpicker)

dataset$keep_flag <- as.data.frame(dataset$keep_flag)

sum(dataset$keep_flag)

final_set <- cbind(dataset,final_flag)

final_set <- final_set[which(final_set$final_flag==1),]

final_set$keep_flag <- NULL

final_set$final_flag<- NULL

This code snippet applies the profit threshold on total no. of items rather than applying it on selected items.

This is the model that got created when I ran @Karsten W. code:

`C1 C2 C3 C4 C5 C6 C7 C8`

Maximize 1200 5600 450 990 1000 560 500 2000

R1 120 45 0 -90 80 120 23 350 >= 100

R2 1 1 1 1 1 1 1 1 = 4

Kind Std Std Std Std Std Std Std Std

Type Int Int Int Int Int Int Int Int

Upper 1 1 1 1 1 1 1 1

Lower 0 0 0 0 0 0 0 0

And the output obtained is:

`item sales profit`

1 A 1200 120

1.1 A 1200 120

1.2 A 1200 120

1.3 A 1200 120

The same item is returned four times. I want 4 unique items. Plus I want to apply constraint of profit as a percentage of Sales generated by those 4 items.

By the way, we kept 'keep_flag' for the similar function to what your 'nitems' is doing. It is a changing variable that takes binary value.

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

Your code seems ok to me, except for that the variable names do not fit to the dataset you provided. In particular it is not clear to me what `keep_flag`

stands for, is that some sort of preselection?

The profit constraint in your code is applied only the four from the solver selected variabes.

Here is your code, a bit cleaned up.

```
library(lpSolveAPI)
dataset <- data.frame(item=LETTERS[1:8], sales=c(1200, 5600, 450, 990, 1000, 560, 500, 2000), profit=c(120, 45, 0, -90, 80, 120, 23, 350))
nitems <- nrow(dataset)
# make lp
lprec <- make.lp(0, ncol=nitems)
set.type(lprec, columns=seq.int(nitems), type="binary")
# set objective
invisible(lp.control(lprec, sense="max")) # sense defaults to "min"
set.objfn(lprec, obj=dataset[, "sales"])
# constraints
min_rel_profit <- 0.10 # min. 10% profit
add.constraint(lprec, dataset[, "profit"]-min_rel_profit*dataset[,"sales"], ">=", 0) # required profit
add.constraint(lprec, rep(1, nitems), "=", 4) # four products
print(lprec)
solve(lprec)
dataset[get.variables(lprec)==1,]
```

The profit constraint is derived as follows (`p`

is the vector of profits, `s`

is the vector of sales, `x`

is the decision variable 0/1, all of length `nitems`

, `minp`

is the minimum relative profit):

- sum(profit) / sum(sales) >= minprofit translates to
`p'x/s'x >= minp`

- this is equivalent to
`(p - minp s)'x >= 0`

Hence the minimum profit has to appear as part of the coefficients on the LHS.