Lookup a Numbered List to find contract that falls within Date From and Date To columns

andygoh
andygoh
in Modeling

I have a numbered list with different pricings with different validity time periods (Date From - Date To) in months.

E.g., Item A

#1 Price = 1000, Date From = Jan 21, Date To = Jun 21

#2 Price = 2000, Date From = Jul 21, Date To = Dec 21

 

I have another module with time period in months and price in rows/columns, as well as quantity to derive final P * Q.

Both the numbered list and module have the same other properties. (Item A, Location, etc.)

 

How would I pull the correct price for each time period?

 

Many thanks!

 

 

 

Tagged:

Answers

  • LipChean_Soh
    LipChean_Soh

    Hi @andygoh 

     

    Without adding a lot of complicated validation, do see if the following works:

    LipChean_Soh_0-1627372315310.png      LipChean_Soh_1-1627372324512.png

    1. 2 lists are created, i.e. Price Transaction and 'SKU 3'. This POC is done in a test model, and i have 'SKU 1' and 'SKU 2' used for other purposes, hence the weird naming.

     

    LipChean_Soh_2-1627372381587.png

    2. 'INP Price Row' is an input module where you enter 'Price, Start Month, End Month'. 'Price Transaction' list is used here.

     

    LipChean_Soh_3-1627372402583.png

    3. 'CAL Price' sends the relevant price to the different months. 'Price Transaction' and 'Month' list are used here.

     

    LipChean_Soh_4-1627372417336.png

    4. I'm assuming 'SKU 2' has the same price in the same month. 'SKU 2' and 'Month' list are used here.

     

    Thanks,

    LipChean

  • andygoh
    andygoh

    Hi @LipChean_Soh, many thanks for your prompt reply.

     

    Have tried following your solution and it works up till the last step, before mapping the numbered list back to the SKU.

    In my case, I am trying to map the numbered list with its parent hierarchical list, so something like:

     

    INP Price Row

    SKU 1

    #1

    #2

    SKU 2

    #3

     

    Where #1 and #2 price, month validity period would only apply to SKU 1.

     

    At 4., INP SKU, I'm unable to get prices to show up by using SKU 3 and month list.

     

    Many thanks and much appreciated.

  • LipChean_Soh
    LipChean_Soh

    Hi @andygoh 

     

    For your SKU hierarchy, are prices entered at SKU 1 level or  #1, #2 level?

    If prices are entered at SKU 1 level, do #1, #2 follow the price of SKU 1?

     

    Thanks,

    LipChean

  • andygoh
    andygoh

    Hi @LipChean_Soh,

     

    Prices are entered at the #1, #2 level, as they will change depending on the date period

  • LipChean_Soh
    LipChean_Soh

    Hi @andygoh 

     

    Some modifications are made.

     

    LipChean_Soh_0-1627867518516.png

     

    LipChean_Soh_1-1627867533690.png.        LipChean_Soh_3-1627867554050.png

    1. 'SKU 3' is the parent hierarchy of 'Price Transaction'.

     

    LipChean_Soh_4-1627867614538.png 

    LipChean_Soh_6-1627867650655.png

    2. There's no change in the 'INP Price Row' module.

     

    LipChean_Soh_5-1627867641702.png. 

    LipChean_Soh_7-1627867664333.png

    3. There's no change in the 'CAL Price' module, except the summary.

     

    LipChean_Soh_8-1627867678219.png

    LipChean_Soh_9-1627867686784.png

    4. There's no change in the 'INP SKU' module.

     

    Thanks,

    LipChean

Categories