Find the minimum contract quantity falling within a contract range

Vignesh Mohan
Vignesh Mohan
in Modeling

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

Tagged:

Answers

  • ArunManickam
    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
    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.

    PriceTier_Step1_List.png

    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.

    PriceTier_Step2_List.png

    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.

     

     

     

     

Categories