topic Creating forecast data based on historical data in QlikView

Creating forecast data based on historical data

Wed, 03 Aug 2011 13:29:18 GMT

I am new to Qlikview and have the following requirement and need some guidance on how to approach it

The user wants to take historical data (Orders for last 6 months), get the total by day of week (Mon, Tues etc) and then get a daily % of the Weekly Total

example:

Historical data	Sun	Mon	Tue	Wed	Thur	Fri	Sat	Weekly total
Data by resource for last 6 months	600	500	400	200	50	780	300	2830
% of weekly total	21%	18%	14%	7%	2%	28%	11%	100%

The next step in the process will then be to take a weekly forecast number (Note that this is not available by day) and apply the % calculated above to this number to get a weekly forecast number

Eg Forcast for week 2800

Forecast	Sun	Mon	Tue	Wed	Thur	Fri	Sat
Forecast for week (2800)	594	495	396	198	49	772	297

This will then be used to calculate a daily variance.

Thanks in advance for help on this

Soreen

Re: Creating forecast data based on historical data

johnw — Wed, 03 Aug 2011 22:01:54 GMT

Add a weekday(Date) as DayOfWeek to your table. Then:

Allocation:
LOAD
DayOfWeek
,sum(Something) as DaySomething
RESIDENT SomeTable
WHERE conditions that narrow you down to the 6 months you want
GROUP BY DayOfWeek
;
LEFT JOIN (Allocation)
LOAD sum(DaySomething) as TotalSomething
RESIDENT Allocation
;
LEFT JOIN (Allocation)
LOAD
DayOfWeek
,DaySomething / TotalSometing as DayAllocationPercent
RESIDENT Allocation
;

Now I'm not sure what the forecast data looks like, but it's possible you'll be able to do something like this:

LEFT JOIN (Forecast)
LOAD
DayOfWeek
,DayAllocationPercent
RESIDENT Allocation
;
LEFT JOIN (Forecast)
LOAD
ForecastKeyField
,DayOfWeek
,WeeklyForecastAmount * DayAllocationPercent as DailyForecastAmount
RESIDENT Forecast
;
DROP TABLE Allocation
;

Re: Creating forecast data based on historical data

Thu, 04 Aug 2011 08:03:35 GMT

Hi John

Thanks for your quick feedback. Will let you know if this solved the problem.

Soreen