2 Replies Latest reply: Aug 4, 2011 4:03 AM by SoreenFourie

# Creating forecast data based on historical data

Hi

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 dataSunMonTueWedThurFriSatWeekly total
Data by resource for last 6 months600500400200507803002830
% of weekly total21%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

ForecastSunMonTueWedThurFriSat
Forecast for week (2800)59449539619849772297

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

Allocation:
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)
RESIDENT Allocation
;
LEFT JOIN (Allocation)
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)
DayOfWeek
,DayAllocationPercent
RESIDENT Allocation
;
LEFT JOIN (Forecast)
ForecastKeyField
,DayOfWeek
,WeeklyForecastAmount * DayAllocationPercent as DailyForecastAmount
RESIDENT Forecast
;
DROP TABLE Allocation
;

• ###### Re: Creating forecast data based on historical data

Hi John

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

Soreen