Find the minimum contract quantity falling within a contract range

Hi,

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.

Requesting a solution for this problem statement

Anaplan_Community_Sample_Scenario_60529.xlsx

Accepted answers

All comments

ArunManickam

Hello @Vignesh Mohan

Can you check this post. It is a similar problem and solution.

https://community.anaplan.com/t5/Anaplan-Platform-Discussions/Matrix-of-Conditions/m-p/51779#M7817

Thanks

Arun

JaredDolich

@Vignesh Mohan

Terrific question!

This is a classic tiering use-case that @DavidSmith documented a best practice in this article.

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:

PriceTier_Step1_System Module.png

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

PriceTier_Step2_System Module BP.png

Here is the system module in grid mode

PriceTier_Step2_System Module Grid.png

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!!

PriceTier_Step3_Calc Module BP.png

System module in grid view. Notice that for an OQ of 26000 we get range 5 which is exactly what we want!

PriceTier_Step3_Calc Module Grid.png

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.

PriceTier_Step4_Target Module BP.png

And the grid view. As you can see each time period correctly obtains the price from our lookup table.

PriceTier_Step4_Target Module Grid.png

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.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…