HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- Calculating weeks on hand based on forecast of sub...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-28-2019
04:24 PM

01-28-2019
04:24 PM

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 ( |

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-29-2019
03:19 PM

01-29-2019
03:19 PM

Re: Calculating weeks on hand based on forecast of subsequent weeks

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.” -Superman

______________________________________________________________________________________________

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-29-2019
03:21 PM

01-29-2019
03:21 PM

Re: Calculating weeks on hand based on forecast of subsequent weeks

I have access to Redshift, yes. It does not necessarily have to be MySQL dataflow.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-29-2019
03:37 PM

01-29-2019
03:37 PM

Re: Calculating weeks on hand based on forecast of subsequent weeks

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.” -Superman

______________________________________________________________________________________________

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

01-29-2019
04:32 PM

01-29-2019
04:32 PM

Re: Calculating weeks on hand based on forecast of subsequent weeks

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

02-07-2019
10:21 AM

02-07-2019
10:21 AM

Re: Calculating weeks on hand based on forecast of subsequent weeks

Could someone help me out with this or provide me with some direction?

Thanks in advance.

Announcements