Calculating weeks on hand based on forecast of subsequent weeks
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  1303  49 
A  Feb 18, 2019  1257  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 24, 2019  0 (there should be at least 10 weeks of Forecast for an item , if not the WOH = 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:
FOR WEEK OF Jan 28, 2019:
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.
FOR WEEK OF Feb,04 2019:
Iteration 1:
650 – 52(FC for week 2) = 598
Weeks on Hand(WOH) = 0 + 1 = 1
Iteration 2:
598 – 49(FC for week 2) = 549
WOH = 1 + 1 = 2
Iteration 3:
549 – 46 (FC for week 3) = 503
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 subsequent weeks and for different items
NOTE: Also 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 Stored Procedures using cursors is the way to go. However I am not really familiar with Stored Procedure.
Is there an alternate method ? Any help will be appreciated.
Thanks in advance.
Prajwal
Comments

Do you have access to Redshift? Or does it need to be a MySQL dataflow?
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman0 
I have access to Redshift, yes. It does not necessarily have to be MySQL dataflow.
0 
I'll try to work on this tomorrow. Can I get an idea of how many Items are in your data set? Are you also forecasting for multiple locations?
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman0 
There are a total of 1172 Items , but all Locations aggregated for an item. So basically just 1 location.
For each item there are upto 72 weeks of data so the dataset has a total of 84K+ rows.
Thanks.
0 
Could someone help me out with this or provide me with some direction?
Thanks in advance.
0
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.3K Connect
 1.1K Connectors
 273 Workbench
 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 524 Datasets
 2.1K Magic ETL
 2.9K Visualize
 2.2K Charting
 434 Beast Mode
 22 Variables
 510 Automate
 114 Apps
 388 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 331 Distribute
 76 Domo Everywhere
 255 Scheduled Reports
 65 Manage
 65 Governance & Security
 1 Product Release Questions
 Community Forums
 40 Getting Started
 26 Community Member Introductions
 67 Community Announcements
 4.8K Archive