Calculating Weeks on Hand for an item based on subsequent weeks of forecast
Hi,
I have the following dataset:
Item | Date | Projected Inventory(PE) | Forecast(FC) |
A | Jan 28, 2019 | 604 | 50 |
A | Feb 04, 2019 | 630 | 52 |
A | Feb 11, 2019 | 500 | 49 |
A | Feb 18, 2019 | 450 | 46 |
A | Feb 25, 2019 | 1176 | 51 |
A | Mar 04, 2019 | 1136 | 55 |
A | Mar 11, 2019 | 1083 | 53 |
A | Mar 18, 2019 | 1030 | 53 |
A | Mar 25, 2019 | 980 | 50 |
A | Apr 01, 2019 | 900 | 65 |
A | Apr 08, 2019 | 853 | 62 |
A | Apr 15, 2019 | 944 | 59 |
A | Apr 22, 2019 | 1303 | 49 |
The Desired O/ P is:
Item | Date | Weeks On Hand |
A | Jan 28, 2019 | 11 |
A | Feb 04, 2019 | 12 |
A | Feb 11, 2019 | 9 |
A | Feb 18, 2019 | 8 |
A | Feb 25, 2019 | 0 (Because there are less than 10 weeks of FC available for Item A) |
A | Mar 04, 2019 | 0 |
A | ... | 0 |
The logic is as follows:
I need to calculate the number of weeks on hand for each item for that particular week based on forecast for several weeks going forward.
So for week of Jan 28, 2019 , the Projected Inventory is 604.
In order to calculate the Weeks On hand of Item A for week of Jan 28, 2019 the calculation is as follows:
1)FOR WEEK OF JAN 28, 2019:
PE = 604
Iteration 1:
604 – 50(FC for week 1) = 554
Weeks on Hand(WOH) = 0 + 1 = 1
Iteration 2:
554 – 52(FC for week 2) = 502
WOH = 1 + 1 = 2
Iteration 3:
502 – 49 (FC for week 3) = 453
WOH = 2 + 1 = 3
And so on…
Finally we get WOH = 11 for Item A for week of Jan 28, 2019.
1)FOR WEEK OF FEB 04, 2019:
PE = 630
Iteration 1:
630 – 52(FC for week 2) = 578
Weeks on Hand(WOH) = 0 + 1 = 1
Iteration 2:
578 – 49(FC for week 3) = 529
WOH = 1 + 1 = 2
Iteration 3:
529 – 46 (FC for week 4) = 483
WOH = 2 + 1 = 3
And so on…
Finally we get WOH = 12 for Item A for week of Feb 04, 2019.
Similarly I need to calculate WOH for different items for subsequent weeks.
NOTE: there should be at least 10 weeks of Forecast for an item , if not the WOH = 0.
How to achieve this result in My SQL?
I am thinking we should use Stored Procedure and Cursors for this calculation. Is Stored Procedure the way to go or is there a simpler solution ?
I am not very familiar with My SQL procedure.
Can someone help me out?
Thanks in advance.
Prajwal
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 98 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 713 Beast Mode
- 50 App Studio
- 39 Variables
- 669 Automate
- 170 Apps
- 447 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 104 Community Announcements
- 4.8K Archive