Multi Variable/Grouping Optimization

[data4accounting]

Hello- 

I have a more complex optimization problem I am trying to solve. I was hoping to be able to use the optimization tool but I'm a bit at a loss if this is possible for my scenario or where to start with setting it up. 

I have attached an example Excel file illustrating a sample dataset to reflect the intended goal and situation. Within this dataset, each line represents a unique transaction indicated by the Unique_ID column in column A. Each line has a spending limit, % Over Spending, and List Price. 

The goal is to put transactions into groupings with the same discount % for all transactions in the group so that each group has a compliance percentage of at least 80%. The groups are based off the % Over Spending column. Ideally the bands (ie minimum percentage threshold to maximum percentage threshold) are as large as possible while still maintaining an 80% compliance. For instance, it would be preferred to have a 20-70% band that has an 80% compliance rate versus a 20-50% band that has a 90% compliance rate so really care more about maximizing band length versus compliance rate as long as the compliance rate is at least 80%. Lastly, would like to be able to say that each band must consist of at least x number of transactions in order to be a grouping. 

Does anyone have any thoughts on this? Thank you! 

[Yoshiro_Fujimori]

Hi @data4accounting ,

Do you want to get [Compliant %] for each [Group]? If so it is not an Optimization problem.

Assuming my understanding is correct, here is a sample workflow to get [Compliant %].

I hope this helps.

Input Data

Workflow

Tile Tool Configuration

With this tool you can effectively group data with any cutoff values.

Output Data

[data4accounting]

No @Yoshiro_Fujimori - the groups here are just example groups and the discount %s are just example percentages which is why they are highlighted in yellow. What I want is to create grouping of transactions based on the % of Over Spending column (ie 20-55%, 55-75%, 75-100%, >100%, etch) and assign a discount % to the grouping where each grouping has at least an 80% compliance rate (constraint #1), x number of transactions (constraint #2) where what I am trying to minimize is the least number of groupings. 

[Yoshiro_Fujimori]

Is the gap between [Spending Limit] and [List Price] the key for grouping. Correct?

If so, I would iterate the process of 

(1) Set a specific discount rate

(2) Output the transactions which are greater than 80% of the Discounted Price

(3) For the remaining transactions, update the discount rate with the maximum ratio.

(4) Back to (1) and iterate the process for the rest of the transactions.

Here is a sample workflow.

Workflow

Macro

Output

You can use [Middle] for grouping the transactions.

I think this process minimizes the number of groups.

I'm not sure if this works for your case, but hope you get some hint from it. Good luck.

[KGT]

This seems like an optimization problem as you are trying to minimise on constraints. The tricky part is that what you are trying to optimise is the groups, so you are clustering with constraints on the clusters kind of... It's not true clustering, as it's only setting cut-offs in a linear list. It's not quite a goal seek problem either.

Dynamically selecting these each time is probably not ideal as you can't compare them. And what happens if the result comes out with 2 groups, 0-99% and 99%-100%, will that be acceptable? I can't think of an example where you wouldn't want to even out the size of the buckets if dynamically setting the group bounds... so it's hard for me to visualise the outcome well, but I definitely haven't seen all the problems in the world. I say all this, because if it's a one-off, you may be better off with a manual method.

I would first do some analysis on your data, which will also help visualise the problem, by:

• Create a field ([FirstBucket] for rounded % (0.2152 will become 22. Might want to revisit this later to make sure rounding is correct rather than FLOOR)
• Summarise on [FirstBucket], with Count on Compliant; Sum on Compliant (This will give you the number of records and the number compliant)
• Sort on [FirstBucket]
• Running total on Count; Sum_Compliant
• Formula : [PercentCompliant] = [RunTot_Sum_Compliant]/[RunTot_Count]
• Add a RecordID and start filtering from certain Record Numbers.

If you get a result from that whereby you are happy, or don't think you'll get better without taking compromising on something else, then stay there. Note: on your sample data, pay attention to what happens at Line 4/5 in that PercentCompliant column.

This highlights an issue, in that 0-49% is a valid bucket, and so is 0-77%, but is one of these preferable over the other? If the whole dataset is 85% compliant, then the whole dataset is a valid bucket. So, having an entirely flexible number of buckets does not really make sense.

That all being said, if you want to use the optimization tool, it's best to take a look at the samples (Help > Sample Workflows > Predictive.... > Prescriptive...) and pick the one that most suits.

Your problem:

Objective: Minimise number of groups

Input Fields:

Unique_ID; %Over Spending; Compliant. (This is all you're using for decision making)

Constraints:

Constraint	Coefficient	lb	ub	type
NumberOfTransactions	1	x (variable to set)	Inf	C
Compliance	1	0.8	1	C

I can't think where to go from there though as both constraints are calculated aggregates on the result, and that doesn't work in my brain.