Find the minimum contract quantity falling within a contract range
Hope you all are having a good day so far.
I would like to identify the least contract quantity falling within a price list range. if 10 contract quantities fall within a price range then I would like to choose the least from it. The catch is that the contract quantity is a calculated value and it is calculated for all the weeks in a year from the current week for all the vendor, material and plant combinations. Please find the attachments for the sample inputs and the expected output.
In this article he use the DISCO method to break up the use-case into modules that take advantage of the Hyperblock engine. So it may seem like a lot of steps but you will find it's very effective and efficient.
I went ahead and translated David's example for you using your use-case.
Summary of Steps:
1. Create a list for your price tiers and a corresponding system module
2. Create a list for all the order quantity possibilities and a corresponding system module.
3. Create a calculation module that relates the order quantities to the price tiers
4. Connect your target module to the calculation module (this will give you the price for the order quantity based on the rules you set forth)
Step 1: Create a price tier list and corresponding system module. Notice there are no list properties. This goes against the best-practice. Instead create a system module that uses the list and add your properties there.
Here is the List. Please add a top level. You will need it later.
Here is the system module:
Step 2: Create a list for all the order quantity possibilities and a corresponding system module. Now, in your case, the order quantities are tiered in thousands so I shortened the all possibilities down by dividing by 1000. You can probably even shorten it further if you feel it's appropriate. Make sure you add the value to the code of the list. We'll be referring to it later.
Here is the list.
Here is the system module in blueprint mode
Here is the system module in grid mode
Step 3. Create a calculation module that relates the order quantities to the price tiers. This is where the real action begins. You can feasibly combine all these line items into one but that would make the formula difficult to audit and wouldn't be following the PLANS methodology. Best to break it up so it's easier to support.
Here is the calculation module in blueprint mode. Notice we create two Booleans to check if the value is less than or greater than the min and max respectively in our pricing tier module. The final Boolean checks if they are both true. If they are we found our price!!
System module in grid view. Notice that for an OQ of 26000 we get range 5 which is exactly what we want!
Step 4.Connect your target module to the calculation module (this will give you the price for the order quantity based on the rules you set forth)
And finally, we pull it all together. Here is the blueprint mode. We first need to obtain the price tier from our calculation module and bring it into our target module. Then we lookup the price.
And the grid view. As you can see each time period correctly obtains the price from our lookup table.
Well, that was fun!
@Vignesh Mohan let us know if this meets your use-case. I know you have some additional nuances but I think this tiering logic will work for you.