I need to do calculation for Week 1 until Week 4 columns in which I already define the date for each weeks. I want to calculate the qty sold for each items with different promos with the limitating conditions on the promo time range (on column promo start and end date) for each promo type on certain items. Other than that, some promo time range is also overlapping so the calculation on qty sold must be done with the level of prioritization for each promo as below:
Buy 1 get 1
Discount
No Promo
To help understand this better, I also attached my manual calculation on Excel: Sample Data.xlsx (12.3 KB)
The main output of the calculation can be seen on column Week 1 until Week 4. I already tried before with Column Expression node but failed to create the logic functions with such many conditions and overlapping dates.
Hello, can you explain the logic you tried in Column Expression?
If you write each step you want to recreate, we can try transforming that into KNIME nodes. Please use a concrete example in each step and perhaps 2 examples to make it really clear what the Sample Data intends to show. Thank you!
Firstly let me attached the raw data: Raw Data.xlsx (11.5 KB)
My calculation steps can be defined as below:
Define date range within a week. My definition for this is that Week 1 will have date range from 1 to 7. Week 2 from 8 to 14. Week 3 from 15 to 21. And week 4 from 22 to 31 (If current month is August).
Calculate the qty sold based on data for each promo type (and no promo). In the raw data, for item ‘Shirt’ had a ‘Discount’ promo type start on August 3 and will finish on August 12. And, a ‘Buy 1 Get 1’ promo type start on August 9 and will finish on August 20. But because there’s an overlap on the promo time range, I must only calculate the promo that I prioritize. The list of promo prioritization is ‘Buy 1 Get 1’ > ‘Discount’ > ‘No Promo’. This will define which ‘qty sold’ data will be used if the promo date is overlapping.
And then, I will calculate the number of qty sold in each weeks based on each promo time range. For example:
Item → Shirt
For Week 1 (August 1-7), there will be a ‘Discount’ promo on August 3 until August 7 (since the promo time range will start on August 3 to August 9).
So, the formula will be:
2 days of no promo (August 1 & 2) * the qty sold for no promo + 5 days of ‘Discount’ promo (August 3-7) * the qty sold for ‘Discount Promo’ => This will return the value of total qty sold of Shirt in Week 1
For Week 2 on the other hand, there will be a slight difference on the calculation. For example:
Item → Shirt
For Week 2 (8-14 August), there will be a ‘Discount’ promo on August 8 until August 12 and a ‘Buy 1 Get 1 Promo’ on August 9 until August 14 (since the promo time range will start on August 9 to August 20).
In this case, there will also be a slight modification in the calculation because I want to choose the type of promo in which I prioritize for the overlapping date. So, the formula will be:
1 day of ‘Discount’ promo (August 8) + 6 days of ‘Buy 1 Get 1’ promo (August 9-14) => This will return the value of total qty sold of Shirt in Week 2
Note that the Shirt qty sold data for ‘Discount’ promo type happening in August 9 until August 12 is not used since during this time the promo date is overlapping with the ‘Buy 1 Get 1’ promo, and it is the promo type that I prioritize.
If you have any other question to clarify the case please do let me know. Thanks in advance!
For the quantity sold: how do I know what dates this corresponds to for No Promo? For instance were 20 shirts sold on August 1-2? What about August 21th onward?
Here is a workflow that shows you how to do what you are trying to accomplish by changing the shape of your data. This workflow still requires more work to be fully compatible with your data, but hopefully this is enough for you to play with and edit yourself to your needs.
So a “No Promo” event will be held in dates that didn’t have promo. So yes, in 1-2 August and 21 onwards (until 31 August) shirt will have a “No Promo”.
