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

andygoh
New Contributor

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

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!

 

 

 

5 REPLIES 5
LipChean_Soh
Certified Master Anaplanner

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

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
New Contributor

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

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
Certified Master Anaplanner

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

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
New Contributor

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

Hi @LipChean_Soh,

 

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

LipChean_Soh
Certified Master Anaplanner

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

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