5 Replies Latest reply on Apr 13, 2015 10:31 PM by Bill Lyons

# How do I create a forecast for demand with start and end dates?

Hello,

I'm trying to create a projection of our demand using the start and end dates. I have built this in excel using a formula that evenly distributed the demand across the months its running using a formula such as this:  =IF(\$I7<=R\$4,IF(\$J7>=R\$3,\$H7/(\$J7-\$I7+1)*(R\$5-IF(\$I7>=R\$3,\$I7-R\$3,0)-IF(\$J7<R\$4,R\$4-\$J7,0)),0),0)

Columns I & J were the start date and end date and then i created columns R-AV as the forecasted months (R = April, S = May, etc), R3, R4, R5 contained the month start and end dates and then the count of days within that month (R3 = 4/1/2015, R4 = 4/30/2015, R5 = 30). H is the budget or demand that is then evenly distributed across the months it's set to run.

My goal is to re-create this type of thing in tableau so that I can just upload a new weekly report and there's nothing I have to do in excel before i connect to tableau.

Has anyone done this before or have any tips or tricks on how to create these forecasted months? Ive attached an image of what my excel looks like right now. Any insight would be greatly appreciated.

Thanks!

Alex

• ###### 1. Re: How do I create a forecast for demand with start and end dates?

Given the appropriate data set, Tableau can provide the forecast for you. Can you upload a packaged workbook (twbx) that represents you issue, with any confidential data removed?

• ###### 2. Re: How do I create a forecast for demand with start and end dates?

Hi Bill,

Attached is an excel example of what I'm trying to do. Ideally though, I would only have to upload an excel of the actual data (columns A - P) and then the forecasted part of the workbook (columns Q - DC would be built within Tableau). The forecast is based on the campaign start and end dates and then it evenly divides the quantity across the amount of days that are run.

• ###### 3. Re: How do I create a forecast for demand with start and end dates?

Interesting. That forecasting is quite different from what Tableau does.

Do you also need to be able to sum the columns (Q - DC)? Are you planning to visualize this in some way (i.e. charts)?

• ###### 4. Re: How do I create a forecast for demand with start and end dates?

The sum of Q - DC is not mandatory, but ideally I would like to be able to build something like this using Tableau and then visualize it in some kind of timeline. Ultimately, we just want to see daily, weekly or monthly demand forecasted based on what's in our pipe.

• ###### 5. Re: How do I create a forecast for demand with start and end dates?

Ok, take a look at the attached. To create the date values, I created a "scaffold" - a set of all valid dates. Then this needs a "cartesian join" - joining every date to every record. Tableau needs a join field, so I created a field called "Link" in both data sets, both full of ones, so joining on these, it produces the cartesian join we need. Then, the work is done in the calculated field "Daily Amount."

Sheet 1 shows the forecast amounts which match your Excel file. Sheets 2 and 3 show some possible visualizations summarizing the data.

I hope this helps. I used Tableau 9.0, because the data interpreter was needed to handle the header of your Excel file.