I build it in in my InvoiceSalesmodel. then I discussed it with my Manager and some Users.
As always they came with new ideas:
The Forcast should predict next 6 months in advance,
Input amouts should be the last twelve months, for instance we are now in November so the Input should be from Last year November untill now and the Forecast should go from now (November 2016 ) till May next year.
I told them that I wished they figured that out earler. I hope you have some ideas?
It's been my experience that I rarely get the real requirements until I've already put a QlikView application in front of them. Until they can start interacting with the data, they either don't know or aren't willing to take the time to figure out what they really need in terms of charts and the like. Some of this is my fault, as it was that way for so long that I now by habit just get the most basic of "we want this data" requirements, and then slam out a very basic QlikView with how I might personally want to interact with that data, then solicit feedback. I usually get at least the data model right the first time (I'm usually very familiar with the data), and it's mostly a matter of building additional charts and ways of interacting with the data from there.
Anyway, that's all an aside. See the attached for a data model solution to your problem, since it seemed that was your preference. I'm sure it could be solved in the chart too if preferred. Here's the script of interest:
CONCATENATE (Data) LOAD monthstart(today(),iterno()-1) as Month ,monthstart(today(),iterno()-1)*Slope+Intercept as Sales ,'Forecast' as Type WHILE iterno() <= 6 ; LOAD linest_m(Sales,Month) as Slope ,linest_b(Sales,Month) as Intercept ; LOAD Month ,sum(Sales) as Sales RESIDENT Data WHERE Month >= monthstart(today(),-12) GROUP BY Month ;
We're doing a linear regression over the past 12 months of data to predict the future six months of data. We also have types of Actual vs. Forecast, and these are associated with colors loaded in the script and used in the background color expression.
The linear trendline in the chart is only to show you that it works, and is based only on the previous 12 months. The trendline doesn't match the forecast when all months are shown, but does match when only the past 12 months and the forecast 6 months are shown.
Just add the dimensions you want to forecast by to the loads where you concatenate the forecast. See attached and script below. Well, this is forecasting 6 months from the past 12, but replace 6 and 12 with whatever you want in this script, and I'd think it would work like you want.
CONCATENATE (Data) LOAD Branch ,Product ,monthstart(today(),iterno()-1) as Month ,monthstart(today(),iterno()-1)*Slope+Intercept as Sales ,'Forecast' as Type WHILE iterno() <= 6 ; LOAD Branch ,Product ,linest_m(Sales,Month) as Slope ,linest_b(Sales,Month) as Intercept GROUP BY Branch ,Product ; LOAD Branch ,Product ,Month ,sum(Sales) as Sales RESIDENT Data WHERE Month >= monthstart(today(),-12) GROUP BY Branch ,Product ,Month ;