Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a requirement that i have no idea how to implement.
I have some forecast data which is by Customer, Item and Period. I then have left joined the standard item prices to the forecast data.
This then allows me to use the forecast volume * the standard price to get a forecast value.
What i need is it to take into account a price change file, so for example..
for Item 01003 and customer 123 i would have the following forecast values
Period | Forecast Value |
1 | 50 |
2 | 201 |
3 | 150 |
4 | 150 |
5 | 150 |
6 | 130 |
7 | 130 |
8 | 130 |
9 | 100 |
10 | 100 |
11 | 100 |
12 | 400 |
13 | 250 |
The standard price for this Item and this customer is 7.35
but in period 6 there will be a price change, the new price will be 7.10.
so what i need is to use the 7.35 for period 1-5 then 7.10 there after.
Period | Forecast Qty | Price | Forecast Value |
1 | 50 | 7.35 | 367.5 |
2 | 201 | 7.35 | 1477.35 |
3 | 150 | 7.35 | 1102.5 |
4 | 150 | 7.35 | 1102.5 |
5 | 150 | 7.35 | 1102.5 |
6 | 130 | 7.1 | 923 |
7 | 130 | 7.1 | 923 |
8 | 130 | 7.1 | 923 |
9 | 100 | 7.1 | 710 |
10 | 100 | 7.1 | 710 |
11 | 100 | 7.1 | 710 |
12 | 400 | 7.1 | 2840 |
13 | 250 | 7.1 | 1775 |
I would appreciate any help given
Finally, I made myself motivated to do this... check it out
Looks like a great candidate for Interval Match.... have you tried that?
Hi Sunny,
I haven't yet, I am unsure how to get the calculations to take the correct price based on the period..
Is period linked to date? How do you know that P6 is when change happen. Because change is based on date 5/24/2018
Yes, it is linked to date. so if you were to select the 24th May, this will filter to P6
Morning Sunny,
did you have any thoughts on this?
morning Sunny,
I don't suppose you've had a chance to think about how i can do this have you?
Today will be the day... will def. get back to you sometime today.
Hi Sunny, I don't suppose you have had any luck with this?
Can anyone help with this?