Straight line Forecast

[joshua haznedar]

Hi there, I have a dataset as shown in table 1, that shows the total number of boxes to be shipped for a store for the time period (e.g. Jan to Mar as 300). In Spotfire I would like to forecast by month so that a new table or row of data gets greated that splits the data as shown in Table 2. My overarching goal is to be able to add up the number of boxes overlaping by month that will be shipped: For example w Jan will have only 100 boxes from store A, but in Feb and Mar there will be a total of 300 boxes for Feb and Mar. What is the best way to do this any help with a calculation would be appreciated. If there is a build in way to do it just based on Table 1 alone that would be fine as well. Thanks.

 

Table 1

 

 

 

Store

Ship Start

Ship End

Number of Boxes

 

 

A

Jan 2019

Mar 2019

300

 

 

B

Feb 2019

Jun 2019

800

 

 

 

 

Table 2

 

 

 

Store

Month Year

Number of Boxes

 

 

A

Jan 2019

100

 

 

A

Feb 2019

100

 

 

A

Mar 2019

100

 

 

 

 

 

 

 

B

Feb 2019

200

 

 

B

Mar 2019

200

 

 

B

May 2019

200

 

 

B

Jun 2019

200

[Khushboo Rabadia]

You can use data functions to achieve your use case as shown in below article:

https://support.tibco.com/s/article/How-to-create-a-continuous-date-rang...

You will need to modify it as per your scenario like number of boxes you can pre-calculate using calculated column (Number of boxes/DateDiff('month', [start Date], [End Date])) and just add in new rows using data function