Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
johnw
Not applicable

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
;

Highlighted
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