Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

2 Replies
MVP
MVP

Re: Creating forecast data based on historical data

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
;

Not applicable

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

Community Browser