I need your help on a project where I could not figure out calculating past 4 weeks' averages and forecasting next week.
I am trying to forecast next week's figure by reviewing past 30 days' figures. I would like to do this by week breakdown.
Today is 20 August 2013 and it is presently week 33 of the year. (2013/33). Here is what I would like to do:
1 - I want to take average of weeks (2013/29-32) and then average its result with week 2013/33.
2 - I have to show this final result at an additional column as I will assume that it will be forecasted cost to me for the present week.
3 - I will always review -30 days from today in week breakdown to forecast week ahead.
4 - I have to stick to product breakdown.
I can put this together in a pivot table but then I cannot apply my desired column that calculates the forecast. I tried set analysis as well but guess I am missing something.
Attached, you will find an excel table with 2 sheets. 1st sheet will be the data, 2nd sheet is illustrating what I am trying to achieve with QlikView. For your convenience, I used inline in the QlikView document for easy reloading.
