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

andygoh

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!

Accepted answers

All comments

LipChean_Soh

Hi @andygoh

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

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.

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

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

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

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

SKU 2

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

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

Hi @LipChean_Soh,

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

LipChean_Soh

Hi @andygoh

Some modifications are made.

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

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

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

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

Thanks,

LipChean

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.…