Need to display missing price ranges along with valid price ranges
Hi Folks,
Wish to hear you are safe & healthy!!!
Got a specification to display the missing price ranges along with valid price ranges in a module where we calculate the price of the contracts based on contract quantity for each supplier & materials. In this module, we calculate the contract quantity and appropriate price based on contract quantity. Able to display the valid price ranges but trying hard to bring in the missing price ranges in the same module. Like to have your guidance for this situation.
1. Contract calculation module is based on weekly time scale
2. Price list is maintained as separate module for each supplier and materials.
3. attached the excel with current module design and the expected outcome
Regards,
Siva
Best Answer
-
Thanks @andre.lie will try these options and update which would fit us. i really appreciate your support and guidance on the same
Regards,
Siva
0
Answers
-
What I think you can do is
- In your quantity module, determine the price range for that quantity by formula. I guess you have done this. How many price range do you have?
- Build the third module (second module is the existing price range module) which is by supplier, material, and range (no timescale). This module is used to determine which range is missing over all periods from the base quantity module
- Build the fourth module which is by supplier, material, range, and week timescale. This will be the final module. It refers to base quantity module, price range module, and the third module. As your quantity module uses Timescale, which week do you plan to put the missing price range?
Based on the steps above, I will have something below as the final module. Here assumption is made that missing range will be placed in the same week as the valid one. For example, in Week 31 there is quantity for Range 2, so missing Range 1 is placed in Week 31 as well. In Week 35 there is quantity for Range 5, so missing Range 4 is in Week 35. In Week 34 there is quantity for Range 3 and there has been quantity for Range 2 in Week 31, so nothing is inserted in Week 34
Regards,
Andre
0 -
Appreciate your timely update for my post. please find the updates for your clarifications:
- Got 16 prize ranges as of now which is different for each products which we are trying to stream line. but need to live with different price ranges for each material and by supplier. price module is non-time scale module. This range count will increase in future
- Give more clarity how you identified the missing ranges from base contract module (time scale against non-time scale) in the third module
- missing prize ranges need to fit the appropriate timescale based on previous range interval (say if the calculated previous interval is between 1000 to 1999 and if the missing range is 2000 to 2999, then this missing range need to follow the above one which is already calculated from base may be the same time period
your solution will surely fit the requirement but considering the current count of products and suppliers, adding Range dimension to this combination will **** the size of the module. As mentioned, each product got its own price list, we are trying to built optimized modules wherever possible, considering size, performance & client constraints on workspace size. Believe you can understand.
Appreciate if you can give us some more options
Regards,
Siva
0 -
Based on my suggested approach earlier, we can use TIMESUM to identify missing range from the fourth module (which is by supplier, maetrial, range, and time) .
You said that missing price range would be placed in the same timescale as the previous range. As in your earlier attachment, there is quantity of 26,000 (Range 3) in Week 34 FY20, and of 37,000 (Range 5) in Week 35 FY20. My question is which week the missing price range 4 should belong to. Based on your explanation, I understand that it should be in Week 34 FY20 i.e. same timescale as previous range. However, we cannot put it in Week 34 unless we have another list as part of the module.
As you said there are 16 price ranges, this is the number of list items that will be in the range list. They can be re-used for each supplier and material combination even though the range value themselves are different for each combination (price module uses supplier and material list). However, if adding a price range list will **** the size of the module (I am not sure how big your supplier and material lists are), another option is to use a running number list item instead of actual range to reduce number of items in that list. The fourth module will be similar to this.
I have not really tried it but think that it can be achieved despite of now requiring a more complex transformation. The number of items in that list can be probably reduced depending on how many missing price ranges can really happen between two adjacent weeks
If there is still problem with the module size, we can probably:
- Make a final module as a one list (numbered list) module with line items only (no timescale)
- With base contract module as source, use action to create the numbered list (supplier, material, and range as code) and load final module
- Create another action to insert the missing ranges. I do not have an exact idea though.
Regards,
Andre
0 -
Hi @andre.lie
Thanks for your thought process and we achieved the expected result with 2-3 step activity
1. built a time series module to calculate the matching price list
2. converted the time series to list
3. built another modules with price list ranges (list) and calcualted the missing price list using the calculated price values
Obviously the size is also under control
Regards,
Siva
0 -
Hi @Sivasankar ,
It's great that you have found the solution. Thanks for sharing the approach as well.
Regards,
Andre
0
