Generating Weekly Schedule from Single Date & Ship Rate

[DVikas]

Hi All ,

I need to generate a Shipping Schedule based on the below Info

Input Data

project #	Project Name	Project Size	SCHEDULED SHIP DATE	Weekly Ship Rate
959885	XXXX	81.3	04/27/2020	8

I am looking at generating date rows based on Reference date stated above with a Gap of 7 Days (as I need to generate Weekly Schedules) , however it should only generate based on below logic 

If Project Size > Weekly Ship Rate then keep adding Date Rows with a gap of a Week until the Summation of Schedules does not exceed Project Size.

It is like generating Date Rows by adding a week to previous Row & also stating the Ship Schedule by ensuring the Ship schedule does not exceed Project Size.

Something like using the No. of Weeks like Division of (Project Size, Weekly Ship Rate) + 1 (if MOD(Project Size , Ship rate) <>0)

Desired Output is below :

project #	Project Name	Project Size	SCHEDULED SHIP DATE	Ship Schedule
959885	XXXX	81.3	04/27/2020	8
959885	XXXX	81.3	05/04/2020	8
959885	XXXX	81.3	05/11/2020	8
959885	XXXX	81.3	05/18/2020	8
959885	XXXX	81.3	05/25/2020	8
959885	XXXX	81.3	06/01/2020	8
959885	XXXX	81.3	06/08/2020	8
959885	XXXX	81.3	06/15/2020	8
959885	XXXX	81.3	06/22/2020	8
959885	XXXX	81.3	06/29/2020	8
959885	XXXX	81.3	07/06/2020	1.3

This is my first Post in the Community as I am a new User. Forgive me for any silly questions 

[vizAlter]

Hi @DVikas — Welcome!

Can you please provide your Excel file with formulas -- Input and desired output files, both?

[bhrmitra · Accepted solution]

Hi @DVikas,

You can use the Generate Rows tool to get the desired result.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks!

[DVikas]

Hey Thanks Abhra . Appreciate your quick reply on this . I will now replicate the logic with large Data Sets.

[DVikas]

HI Abhra,

I added by other Dataset & works fine. However , I wanted to Re-arrange the Scheduled Ship dates horizontally . I tried doing the Cross Tab , however , everything shows as Null.

How can I fix this ?

[bhrmitra]

Hi DVikas,

Can you provide me with an example of your desired output?

Thanks,

Abhra Mitra

[DVikas]

Hi Abhra ,

Below is the desired output

project #	Project Name	Project Size	SCHEDULED SHIP DATE	04/27/2020	05/04/2020	05/11/2020	05/18/2020	05/25/2020	06/01/2020	06/08/2020	06/15/2020	06/22/2020	06/29/2020	07/06/2020
959885	XXXX	81.3	04/27/2020	8	8	8	8	8	8	8	8	8	8	1.3

[bhrmitra]

Hi,

This is easy. Using the crosstab tool properly and then renaming the headers dynamically can help you achieve your desired output.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks!

[DVikas]

Hi Abhra ,

I have another requirement of basically having 2 Schedules for a particular Project , however I need to give priority to a specific Schedule , if available for the project.

Input 1

Project	07-Sep	14-Sep	21-Sep	28-Sep	05-Oct	12-Oct	19-Oct	26-Oct	02-Nov	09-Nov	16-Nov	23-Nov
XXXX	10	10	10	10	10	10	10	10	10	10	10	10

Input 2

Project

07-Sep

14-Sep

21-Sep

28-Sep

05-Oct

12-Oct

19-Oct

26-Oct

02-Nov

09-Nov

16-Nov

23-Nov

XXXX

20

20

20

20

20

20

Desired Output: Input 2 (if Available, with its Schedule)

Project

07-Sep

14-Sep

21-Sep

28-Sep

05-Oct

12-Oct

19-Oct

26-Oct

02-Nov

09-Nov

16-Nov

23-Nov

XXXX

20

20

20

20

20

20