So I would say, this became a very difficult problem based on this one piece (what you do in future dates, those beyond 1 week in the future). You say:
For instance, if the future date is Wed, than I want to calculate the the average of the past 4 Weds. (and for the following Wed, use the past 4 weeks, but one week will have to be the Wed which was already a forecasted value)
So you want to have the calculation be of the past 4 weeks, but some or all of those weeks might already be calculated as an average of the past 4 weeks. So it's an average of some or more windowed averages. It starts to become very complicated, so I wanted to post a simple solution here, and start on a more complex one so you can see the overall pattern.
First, see the attached Excel workbook for the demo data. I took 6 months of dates, both past and present, and created a random Sales figure. In the fields SimpleCalc and ComplexCalc, I've done two versions of the calculations I'm trying to recreate in Tableau. (These weren't imported into the workbok).
In the attached workbook, sheet "SimpleCalculation" has this basic calculation of just the past 4 weeks.
It involves these pieces:
- In the Rows: Date (daily) as an Attribute, and a calculated Weekday field
- In Level of Detail: a calculated Week field (this is what your windowed functions will be calculated over)
- The required Calculated Fields are shown in stages here:
- Sales-WeekdayAverage is the window average of each weekday (calculated field along DateWeek)
- Sales-WeekdayAveragePast4Weeks is this window average restricted to just the past 4 weeks, not including the current week - this is the (-4,-1) piece in the calculation WINDOW_AVG(SUM([Sales]),-4,-1) (also calculated along DateWeek)
- SalesOrSalesAvg uses this second calculation if it is a future date, and sales if the present. This is the field you're looking for.
Now for the far more difficult piece of this, which is using the already calculated forecast instead of the actuals where it exists. The way I am solving this involves 2 calculations for every week into the future. Hopefully you can see the basic pattern here.
For example, Week 1:
- IF MAX([DateCheck])="Future" THEN [Sales-WeekdayAveragePast4Weeks(1WeekOut)] ELSE SUM([Sales]) END
- IF MAX([DateCheck])="Future" THEN [Sales-WeekdayAveragePast4Weeks(2WeekOut)] ELSE SUM([Sales]) END
And so forth with Week 3, Week 4, and so on. Hopefully you can see the pattern that this is going in the workbook. Note every single one of these should be calculated along DateWeek.
Obviously this would get really big if you're forecasting out 3 years out - though at a certain point I believe should converge into the same values for each weekday as your forecast (which is actually a really interesting math problem, on how long that would take - but I digress )
Joe Mako covers this in a presentation he did for TDT. The video link can be found on the bottom bar (called Domain Padding) of the viz found here:
goes step by step but you'll have to custom tailor for your own data. very helpful!
Joe's presentation for TDT is fantastic, and a great video to watch. However, it doesn't actually cover the issue brought up in the question, of calculating future values based off of previously calculated values in the manner discussed.
What is does address is missing values of dates, whether in the past or the future. This is a great thing to use if the future dates do not exist in the original data source (which wasn't specified - I assumed they did in my response).
No matter whether the data is in the original data source or not, however, the problem is that you are trying to perform a calculation on a field that may be previously calculated the same way. It becomes an odd loop as far as I can see, though it is solvable. There may be a better/different way to calculate it - and hopefully someone will jump in if they have other ideas on how to do this!!